Fetch 3 latest details from table

Question

I want to fetch 3 latest devices for a person. Table structure is shown below

PERSON_DEVICE

person_id  seq_num   device_detail device_detail_added
999         1           Nexus        03/04/2015
999         2            iphone       06/07/2015
999         3            laptop       08/09/2015
999         4            uda           02/01/2016
999         5            pda            04/04/2016
1001        1            sm             03/03/2015 
... and so on

For latest device for a person I can use following query

select * from PERSON_DEVICE
where person_id = 999
  and seq_num = (select max(seq_num) from PERSON_DEVICE where person_id = 999)

but for second latest and third latest, I don`t know how to get this ?

I tried using limit, but in Sybase it is not working (for my Sybase version).

P.S. seq_num is used to identify latest device for a particular person id.

********************EDIT*****************************************88

 PERSON 
person_id  name   addres
999         john   2 avenue
1001      elliot    rrt

I want to  records to 

select   pn.name , pd.device1 ,pd.device_detail_added1, pd.device2 ,pd.device_detail_added2,pd.device3 ,pd.device_detail_added3 from  PERSON pn , PERSON_DEVICE pd

This is tagged Oracle and sybase - which one is the question for? If it is sybase then please hover the mouse over the sybase tag, read the text and edit the question to pick the correct tag for the version you are using. — MT0, Jun 21 '16 at 09:30
If this is sybase then duplicate of [Is there a way to find TOP X records with grouped data?](http://stackoverflow.com/q/2953724/1509264) and if this is Oracle then [Get top results for each group](http://stackoverflow.com/q/134958/1509264) (see the highest voted answer not the accepted answer). — MT0, Jun 21 '16 at 09:43

score 0 · Answer 1 · answered Jun 21 '16 at 09:16

0

If seq_num is used to fetch latest used device, then the following query should work:

SELECT TOP 3 *
FROM PERSON_DEVICE 
WHERE person_id=999
ORDER BY seq_num DESC

answered Jun 21 '16 at 09:16

Sujeet Sinha

2,417
2
18
27

score 0 · Answer 2 · answered Jun 21 '16 at 09:17

This should work on all person_id (removing where person_id=999)

select * 
  from (
        select person_id , device_detail, device_detail_added, seq_num ,
               row_number() over (partition by person_id order by seq_num desc) as rango
           from PERSON_DEVICE 
          where person_id=999) as t
where rango <=3;

score 0 · Answer 3 · answered Jun 21 '16 at 09:21

0

in oracle db you can use below to get third latest :

SELECT * FROM (SELECT PERSON_DEVICE.*, ROW_NUMBER() OVER(ORDER BY SEQ_NUM DESC) AS TARGET_NO FROM PERSON_DEVICE WHERE PERSON_ID = 999) WHERE TARGET_NO <= 3

answered Jun 21 '16 at 09:21

Ming

211
1
6

Fetch 3 latest details from table

3 Answers3