CODEWITHSUNDEEP
sqlsql-serverdatabase
John Constantine
John Constantine

Reputation: 1092

SQL query to get recent items

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

Upvotes: 0

Views: 207

Answers (3)

ShivShankar Namdev
ShivShankar Namdev

Reputation: 298

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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;

Upvotes: 4

Jebik
Jebik

Reputation: 788

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

Upvotes: 0

Related Questions