0

Assume I have the following pretty big table (350+ million rows): 

Create table test(
col1 int, -- there is an index on this column
col2 datetime,
...
...

)

Sometimes I want to be able to pull only records that match against col1 however since there are duplicates, I want only the ones with the latest timestamp.

For example:

select * from test where col1 in (123, 389, 192) AND only give me the record for each match against col1 that has the latest timestamp.

So in a table that contains: 

123, 2015-08-23,....
123, 2015-09-23,....

it would return only the second record for value 123 which has a date of 2015-09-23.

Thanks

Mo.
  • 40,243
  • 37
  • 86
  • 131

1 Answers1

1

Use a derived table to get the max date for each col1 and join the results back to the main table.

select t.* 
from test t
join (select col1, max(col2) as maxdate from test group by col1) t1
on t1.col1 = t.col1 and t1.maxdate = t.col2
where t.col1 in (123, 389, 192)
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • I should have probably put this in the question but this table can get pretty big (350 million rows) so I would be worried about the performance of this join. – Mo. Nov 23 '15 at 21:38