How to use top 1 with group by

Question

Need to query latest record of each ID. Tried Max, but it seems not possible to have col2 in output. Also, the top 1 function not worked here. Here is the demo table:

create table #demo (ID INT,col1 DATETIME,col2 INT)

INSERT INTO #demo SELECT 1, '2024-01-29', 100
INSERT INTO #demo SELECT 1, '2024-01-28', 123
INSERT INTO #demo SELECT 1, '2024-01-27', 154
INSERT INTO #demo SELECT 2, '2024-01-29', 178
INSERT INTO #demo SELECT 2, '2024-01-28', 200
INSERT INTO #demo SELECT 2, '2024-01-25', 90

Answer 1

Hi @Y.N If I understand right, you could use Row_number here.

Check this:

SELECT ID,col1,col2
FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY col1 DESC)AS RNum FROM #demo)S
WHERE S.RNum=1

Best regards, Cosmog Hong