Sql query to find sorted and group by id

Question

I have one table named personAndBookinMapping columns are : mappingId person_id book_id intreset_score book_name

Now I have to find top two record based on interest_score of each person_id. basically response should be like below :

mapping_id person_id book_id intreset_score book_name enter link description here

How do you handle ties? – Gordon Linoff Sep 23 '19 at 10:33 — Gordon Linoff, Sep 23 '19 at 10:33

score 0 · Answer 1 · answered Sep 23 '19 at 10:33

You would typically do this using window functions, which are available in MySQL 8+.

select pabm.*
from (select pabm.*,
             row_number() over (partition by person_id order by interest_score desc) as seqnum
      from personAndBookinMapping pabm
     ) pabm
where seqnum <= 2;

In earlier versions, variables are the most consistent method:

select pabm.*
from (select pabm.*,
             (@rn := if(@p = person_id, @rn + 1,
                        if(@p := person_id, 1, 1)
                       )
             ) as rn
      from (select pabm.*
            from personAndBookinMapping pabm
            order by person_id, interest_score desc
           ) pabm cross join
           (select @p := -1, @rn := 0) params
     ) pabm
where rn <= 2;

Sql query to find sorted and group by id

1 Answers1