SQL Server SQL - show only the row with the latest date

k4ymrczo 于 2023-08-02 发布在其他

关注(0)|答案(3)|浏览(106)

I am having an issue with what I thought would be pretty simple - I have a table, with sometimes several rows for the same entry corresponding to different dates. I would like to write a query to get only one row per entry, and always with the latest date. The latest date can be different for different entries.

For example, if the table looks like this :

+------+------+------------+---------+
| SKU  | Flag |    Date    | Version |
+------+------+------------+---------+
| SKU1 | Up   | 2020-02-01 | V2      |
| SKU2 | Up   | 2020-01-01 | V2      |
| SKU2 | Down | 2020-04-01 | V2      |
| SKU3 | Up   | 2020-04-20 | V2      |
| SKU3 | Down | 2020-03-01 | V2      |
| SKU4 | Down | 2020-01-01 | V1      |
+------+------+------------+---------+

I would like to return that :

+------+------+------------+---------+
| SKU  | Flag |    Date    | Version |
+------+------+------------+---------+
| SKU1 | Up   | 2020-02-01 | V2      |
| SKU2 | Down | 2020-04-01 | V2      |
| SKU3 | Up   | 2020-04-20 | V2      |  |
+------+------+------------+---------+

I tried several things but couldn't get what I wanted...the last thing I tried was something like this :

SELECT distinct sku,flag,version,max(date) AS latest_date
FROM table
GROUP BY sku,flag,version
HAVING version='V2'

However doing that I still get several entries for the same SKU with different dates... What am I doing wrong ? Also the date in the table is a VarChar type - I tried converting it to a date type (is it necessary?) replacing max(date) by max(convert(varchar,date)), but without success

Thank you in advance !

sql-server

来源：https://stackoverflow.com/questions/61350684/sql-show-only-the-row-with-the-latest-date

3条答案

按热度按时间

dy1byipe1#

A simple method with good performance is a correlated subquery:

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.sku = t.sku);

For performance, you want an index on (sku, date) .

赞(0）回复(0）举报 2023-08-02

ccrfmcuu2#

One option is to use WITH TIES

Example

Select top 1 with ties *
 From  YourTable
 Order By row_number() over (partition by SKU Order by [Date] desc)

赞(0）回复(0）举报 2023-08-02

2guxujil3#

You can use row_number() :

select *
from (
    select t.*, row_number() over(partition by sku order by date desc) rn
    from mytable t
) t 
where rn = 1
order by sku

Note that your expected result is missing a row for 'SKU4' - I assume that's a typo.

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server SQL - show only the row with the latest date

3条答案

相关问题

热门标签

最新问答