Select only the most recent datarows

Question

I have a table that takes multiple entries for specific products, you can create a sample like this:

CREATE TABLE test(
    [coltimestamp] [datetime] NOT NULL,
    [col2] [int] NOT NULL,
    [col3] [int] NULL,
    [col4] [int] NULL,
    [col5] [int] NULL)
GO

Insert Into test 
values ('2021-12-06 12:31:59.000',1,8,5321,1234), 
('2021-12-06 12:31:59.000',7,8,4047,1111),
('2021-12-06 14:38:07.000',7,8,3521,1111),
('2021-12-06 12:31:59.000',10,8,3239,1234),
('2021-12-06 12:31:59.000',27,8,3804,1234),
('2021-12-06 14:38:07.000',27,8,3957,1234)

You can view col2 as product number if u like. What I need is a query for this kind of table that returns unique data for col2, it must choose the most recent timestamp for not unique col2 entries.

In other words I need the most recent entry for each product

So in the sample the result will show two rows less: the old timestamp for col2 = 7 and col2 = 27 are removed

Thanks for your advanced knowledge

This has been asked and answered so many times. Use ROW_NUMBER — Sean Lange, Dec 06 '21 at 15:16

score 1 · Accepted Answer · answered Dec 06 '21 at 15:34

1

Give a row number by ROW_NUMBER() for each col2 value in the descending order of timestamp.

;with cte as(
  Select  rn=row_number() over(partition by col2 order by coltimestamp desc),*
   From table_name
)
Select * from cte
Whwre rn=1;

answered Dec 06 '21 at 15:34

Ullas

11,450
4
33
50

Thank you, your method works. I found an interesting workaround aswell with a join on subset of the same table, idk if thats also a viable option – Dec 06 '21 at 15:44

Select only the most recent datarows

1 Answers1