CODEWITHSUNDEEP
sqlmariadb
Pranjal
Pranjal

Reputation: 9

I want to fetch 2 records with same id

Id  user_id   Name
--- --------  ------
1      1        A
2      1        B
3      1        C
4      2        D
5      2        E
6      2        F

This is my user table and i want to fetch 2 rows in descending using user_id. I want output like.

Id  user_id   Name
--- --------  ------
2      1        B
3      1        C
5      2        E
6      2        F

Thanks in advance

Upvotes: 0

Views: 227

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

I think you strictly need an ANCIer approach. You should try below co-related sub query - 

SELECT Id, user_id, Name
FROM  T T2
WHERE (SELECT  COUNT(*)
       FROM    T T3
       WHERE T2.user_id = T3.user_id
       AND T2.Id <= T3.Id) <= 2

Fiddle.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use row_number():

select Id, user_id, Name
from (select t.*, row_number() over (partition by user_id order by id desc) as seqnum
      from t
     ) t
where seqnum <= 2
order by user_id, id

Upvotes: 2

Related Questions