SQL query to get recent items

Question

I have a sql table

id  item    date
A   apple   2017-09-17
A   banana  2017-08-10
A   orange  2017-10-01
B   banana  2015-06-17
B   apple   2014-06-18

How do I write a sql query, so that for each id I get the two most recent items based on date. ex:

id  recent second_recent   
a   orange  apple
b   banana  apple

Which DBMS are you using? The answer is different for different systems. mysql <> sql server. — Sean Lange, Oct 25 '17 at 15:14
I removed the incompatible database tags. SQL Server and MySQL are not the same thing. — Gordon Linoff, Oct 25 '17 at 15:19
@Randy I wasn't able to come up with a logic on how to do this. — John Constantine, Oct 25 '17 at 15:26

score 4 · Accepted Answer · answered Oct 25 '17 at 15:35

You can use row_number() and conditional aggregation:

select id,
       max(case when seqnum = 1 then item end) as most_recent,
       max(case when seqnum = 2 then item end) as most_recent_but_one,
from (select t.*, 
            row_number() over (partition by id order by date desc) as seqnum
      from t
     ) t
group by id;

score 0 · Answer 2 · answered Oct 25 '17 at 15:20

Like said on: SQL: Group by minimum value in one field while selecting distinct rows

You must use A group By to get min

SELECT mt.*,     
FROM MyTable mt INNER JOIN
    (
        SELECT item AS recent, MIN(date) MinDate, ID
        FROM MyTable
        GROUP BY ID
    ) t ON mt.ID = t.ID AND mt.date = t.MinDate

I think you can do the same with a order by to get two value instead of one

score 0 · Answer 3 · answered Oct 25 '17 at 16:07

You can use Pivot table

SELECT first_column AS <first_column_alias>,
 [pivot_value1], [pivot_value2], ... [pivot_value_n]
 FROM
 (<source_table>) AS <source_table_alias>
 PIVOT
 (
 aggregate_function(<aggregate_column>)
 FOR <pivot_column> IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
 ) AS <pivot_table_alias>;

Learn More with example here Example

SQL query to get recent items

3 Answers3