选择具有最大列值的每个索引值一行

发布于 2024-10-16 11:30:49 字数 595 浏览 3 评论 0原文

通过具有以下字段的表设置：

SKU, EVENTSTARTDATE, EVENTENDDATE, PRICE, (...etc...)

并容纳数千行，这里是示例数据（日期为 YYMMDD，世纪代码除外）：

1111111, 101224, 101231, 10.99
1111111, 110208, 110220, 9.99
1111111, 110301, 110331, 8.99
2222222, 101112, 101128, 15.99
2222222, 101201, 110102, 14.99
etc

我希望有一个 SELECT 语句为每个 SKU 返回一行，其中最大 EVENTSTARTDATE 没有 WHERE子句隔离特定 SKU 或不完整的 SKU 子集（所需的 SELECT 语句应为所有 SKU 的每个 SKU 返回一行）。我最终想添加开始日期小于或等于当前日期和结束日期大于或等于当前日期的标准，但我必须先从某个地方开始。

所需的示例结果（目前只是最大日期）：

1111111, 110301, 110331, 8.99
2222222, 101201, 110102, 14.99
etc.

原文

With a table setup with the following fields:

SKU, EVENTSTARTDATE, EVENTENDDATE, PRICE, (...etc...)

and housing thousands of rows here is example data (dates are YYMMDD, century code excluded):

1111111, 101224, 101231, 10.99
1111111, 110208, 110220, 9.99
1111111, 110301, 110331, 8.99
2222222, 101112, 101128, 15.99
2222222, 101201, 110102, 14.99
etc

I'd like to have a SELECT statement return one row per SKU with the maximum EVENTSTARTDATE without having a WHERE clause isolating a specific SKU or incomplete subset of SKUs (desired SELECT statement should return one row per SKU for all SKUs). I'd eventually like to add the criteria that start date is less than or equal to current date, and end date is greater than or equal to current date, but I have to start somewhere first.

Example results desired (for now just max date):

1111111, 110301, 110331, 8.99
2222222, 101201, 110102, 14.99
etc.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

诺曦 2024-10-23 11:30:49

从最新版本的 DB2 开始，您可以使用分析函数 ROW_NUMBER()

SELECT * 
FROM (
    SELECT 
        tablename.*, 
        ROW_NUMBER() OVER (PARTITION BY sku 
                           ORDER BY eventstartdate DESC) As RowNum
        FROM tablename) X 
WHERE X.RowNum=1

对于每个分区（SKU 组），数据按照 order by eventstartdate desc 进行行编号，因此 1,2,3, ...最新的 EventStartDate 从 1 开始。然后，WHERE 子句仅选取每个 SKU 的最新版本。

From recent versions of DB2, you can use the analytical function ROW_NUMBER()

SELECT * 
FROM (
    SELECT 
        tablename.*, 
        ROW_NUMBER() OVER (PARTITION BY sku 
                           ORDER BY eventstartdate DESC) As RowNum
        FROM tablename) X 
WHERE X.RowNum=1

For each Partition (group of SKU), the data is row numbered following the order by eventstartdate desc, so 1,2,3,...starting from 1 for the latest EventStartDate. The WHERE clause then picks up only the latest per SKU.

回复收藏 0 原文

指尖凝香 2024-10-23 11:30:49

查看 GROUP BY 和 HAVING 子句。

select sku, max(eventstartdate)
FROM TABLE
group by sku
having eventstartdate <= sysdate

编辑：添加 HAVING 语句

Have a look at GROUP BY and HAVING clauses.

select sku, max(eventstartdate)
FROM TABLE
group by sku
having eventstartdate <= sysdate

Edit: added HAVING statement

回复收藏 0 原文

猫弦 2024-10-23 11:30:49

其他解决方案

 select distinct f3.* 
 from  yourtable f1
 inner join lateral
       (
        select * from yourtable f2
        where f1.SKU=f2.SKU
        order by EVENTSTARTDATE desc, EVENTENDDATE desc
        fetch first rows only
        ) f3 on 1=1