Order By and Limit on columns

Question

I have a table vehicles with these columns

id  license_number created_at                   updated_at                   pump_number
1   ABC123         2014-10-28 13:43:58.679812   2014-10-28 13:43:58.679812   1
2   XYZ224         2014-10-29 05:24:18.163042   2014-10-29 05:24:18.163042   2
3   AB1111         2014-10-29 06:50:56.891475   2014-10-29 06:50:56.891475   1
4   AB1112         2014-11-10 06:20:06.666361   2014-11-10 06:20:06.666361   1
5   ABC123         2014-11-10 06:21:10.160651   2014-11-10 06:21:10.160651   1
6   XXU111         2014-11-10 06:33:57.813795   2014-11-10 06:33:57.813795   2
7   ABC323         2014-11-11 09:02:48.509402   2014-11-11 09:02:48.509402   1
8   YYY123         2014-11-12 06:12:13.851119   2014-11-12 06:12:13.851119   2
9   ZZZ123         2014-11-12 06:12:42.099546   2014-11-12 06:12:42.099546   2

where id is primary key and autoupdated.

I want to fetch two last recent updated_at records of every pump_number in table

So query should return me only row with IDs 9,8,7,5

Answer 1

I found similar question here. check that for reference.

This should work for you :

SELECT *
FROM   vehicles t1
WHERE  (SELECT Count(*)
        FROM   vehicles t2
        WHERE  t1.pump_number = t2.pump_number
               AND t1.updated_at < t2.updated_at) < 2 

Answer 2

Below query will return last 2 records for every pump number:

select * from (select * from vehicles  order by updated_at desc)a group by pump_number 
Union 
select * from (select * from vehicles  order by updated_at desc where id not in 
(select id from (select * from vehicles  order by updated_at desc) group by pump_number)b
)c group by pump_number 

Answer 3

select id,pump_number from (
select id,pump_number,row_number() over(partition by pump_number order by updated_at 
desc  ) rn from tempd ) 
where rn<=2
order by 1;

You can try this using analytical function as i did it in oracle sql.