Max date in SQL Server consultation

r6hnlfcb 于 2023-08-02 发布在 SQL Server

关注(0)|答案(4)|浏览(153)

I need to get the row with the maximum date by the ItemKey .

I need the whole row.

I have this table:

num | ItemKey | Serial | Qty | ItemName | Tdate
----+---------+--------+-----+----------+-------------------------
1   | 111     | 5      | 10  | AAA      | 2010-03-25 00:00:00.000 
2   | 111     | 0      | 12  | AAA      | 2010-03-26 00:00:00.000 
3   | 222     | 6      | 13  | BBB      | 2010-03-25 00:00:00.000 
4   | 222     | 2      | 11  | BBB      | 2010-03-28 00:00:00.000 
5   | 333     | 3      | 15  | CCC      | 2010-03-25 00:00:00.000 
6   | 333     | 4      | 16  | CCC      | 2010-03-26 00:00:00.000 
7   | 333     | 0      | 17  | CCC      | 2010-03-27 00:00:00.000

I need to get this:

num | ItemKey | Serial | Qty | ItemName | Tdate
----+---------+--------+-----+----------+--------------------------
2   | 111     | 0      | 12  | AAA      | 2010-03-26 00:00:00.000 
4   | 222     | 2      | 11  | BBB      | 2010-03-28 00:00:00.000
7   | 333     | 0      | 17  | CCC      | 2010-03-27 00:00:00.000

I tried this SQL statement:

select * 
from MyTBL 
where Tdate = (select MAX(Tdate) from MyTBL)

But unfortunately it does not work

Thanks

sql-server

来源：https://stackoverflow.com/questions/47759649/max-date-in-sql-server-consultation

4条答案

按热度按时间

gdrx4gfi1#

you can use ROW_NUMBER to achieve this

SELECT * FROM (
select *,
       ROW_NUMBER() OVER (PARTITION BY ItemKey  ORDER BY Tdate DESC) as rn from MyTBL) AS T1
WHERE rn = 1

or in another way (using common table expressions)

WITH CTE_1 AS (
    select *,ROW_NUMBER() OVER (PARTITION BY ItemKey  ORDER BY Tdate DESC) as rn from MyTBL)
SELECT * FROM CTE_1 WHERE rn = 1

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

pes8fvy92#

Just try like this;

select * from MyTBL M1 inner join
    (select ItemName,max(Tdate) as Tdate from MyTBL group by ItemName) M2
    ON M1.ItemName = M2.ItemName and  M1.Tdate = M2.Tdate

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

vxbzzdmp3#

SQL HERE

You can use this :

select t1.* from table_name t1
join (select ItemKey, max(Tdate) Tdate from table_name group by ItemKey) as t2 
      on t1.ItemKey=t2.ItemKey and t1.Tdate=t2.Tdate
order by t1.ItemKey

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

jslywgbw4#

Try this

WITH t AS ( SELECT *, 
RANK() OVER (PARTITION BY ItemName ORDER BY TDate DESC ) as myRank FROM MyTBL)
SELECT [num], [ItemKey], [Serial], [Qty], [ItemName], [TDate] FROM t 
WHERE t.myRank = 1

SQL fiddle

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

我来回答

Max date in SQL Server consultation

4条答案

相关问题

热门标签

最新问答