0

Say I have the following table:

Type        |Price   |Time      
-------------------------
Drill       | 5      | ---
Drill       | 2      | ---
Drill       | 1      | ---
Plant       | 3      | ---
Plant       | 4      | ---
Light Plant | 9      | ---
Light Plant | 21     | ---
Light Plant | 1      | ---
Light Plant | 5      | ---

I would like to get the 2 most recent records of each Type. So this is the output I'm expecting:

Drill       | 5
Drill       | 2
Plant       | 3
Plant       | 4
Light Plant | 9
Light Plant | 21

I have a working solution with multiple queries i.e. each query looks for the 2 most recent records of a specific Type. Loop over all the unique Types. However, I want to do it with a single query.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Berry Jones
  • 857
  • 2
  • 7
  • 11
  • 1
    *... assuming the table is ordered* there is no inherent row order in SQL tables. You need to specify a column that determines order. Do you have such a column in your table? – Giorgos Betsos Mar 28 '17 at 05:42
  • I do. Sorry I wasn't clear. What I meant is _assuming the table is ordered, this is the **result** I'm expecting_. I do have a column that determines order (a timestamp) – Berry Jones Mar 28 '17 at 05:54
  • 2
    Possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – Shadow Mar 28 '17 at 06:06
  • It is not a duplicate. I want to get the 2 most recent for each Type. The existence of any Type on the table should not affect any other one. i.e. the records of a particular type shouldn't depend on any of the other Types. Also it should be oblivious to the actual data (get the two recent ones without knowing anything about their content other than the fact they're of a particular Type) – Berry Jones Mar 28 '17 at 06:23

0 Answers0