0

I reviewed Selecting latest entries for distinct entry but I struggled because I had more data on the table that I needed in the result set.

We are creating rules around several sets of product, and we need to pull the LATEST rule for each CLASS_ID. By latest, i'm referring to the latest entry_id.

ENTRY_ID....TIMESTAMP....USER_ID....CLASS_ID....PRICE_POINT
1...........3/2/2018 3:40...1..........53.......50
2...........3/2/2018 3:56...1..........12.......50
3...........3/2/2018 4:56...1..........24.......22
4...........3/2/2018 4:57...1..........564.....22
5...........3/3/2018 4:08...1..........53.......99
6...........3/3/2018 4:09...1..........53.......99

The goal is to get the LATEST timestamp or entry (they should correspond with each other) for EACH class.

The desired output is (ordered by class):

TIMESTAMP....USER_ID....CLASS_ID....PRICE_POINT
3/2/2018 3:56...1..........12.......50
3/2/2018 4:56...1..........24.......22
3/3/2018 4:09...1..........53.......99
3/2/2018 4:57...1..........564.....22

I've spent a few hours looking at this, and it seems really simple, but i've struggled to find a way to work through it.

There will not be a lot of growth, few thousand rows max, so i'm looking for simple code to understand and learn from over performance.

Thanks!

Kyle

kpretsch
  • 27
  • 1
  • 4

1 Answers1

1

Here is one common method:

select t.*
from t
where t.entry_id = (select max(t2.entry_id) from t t2 where t2.class_id = t.class_id);

With an index on (class_id, entry_id), this is often the fastest solution.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786