CODEWITHSUNDEEP
mysqlsqldatabase
mstaal
mstaal

Reputation: 630

Get top N rows of each group in MySQL

Given a MySQL table of the form

Name   | Type
-------+-----
Bill   | A
Hill   | B
Jill   | C
Hans   | A
George | C
Sophie | B
Hannah | B
Nancy  | C
Phil   | A
...    | ...

I would like to produce a MySQL query which provides me with the top N rows grouped by their type. By 'top' I mean with respect to a given ordering. In this example, it could be the order given by ordering the type parameters alphabetically (or by date, if all type parameters are dates). For instance, if N = 2, then the resulting table could be:

Name   | Type
-------+-----
Bill   | A
Hill   | B
Jill   | C
Hans   | A
George | C
Sophie | B
...    | ...

That is, the entries may very well be grouped into their respective types in the resulting tables, but it is not strictly important that they are. I run MySQL 8.x.

Upvotes: 0

Views: 2603

Answers (2)

Md. Zahangir Alam
Md. Zahangir Alam

Reputation: 557

Use ROW_NUMBER() and PARTITION BY as whown below.

In my case, the table name was company_perform_quter. I used group by single column, order by multiple column and received latest four record for each group.

SELECT* from (SELECT *, ROW_NUMBER() OVER( PARTITION BY symbol ORDER BY symbol, year DESC, month DESC, quater DESC) rowNum
FROM company_perform_quter) nn WHERE rowNum<=4

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If you want n rows per group, use row_number(). If you then want them interleaved, use order by:

select t.*
from (select t.*,
             row_number() over (partition by type order by name) as seqnum
      from t
     ) t
where seqnum <= 2
order by seqnum, type;

This assumes that "top" is alphabetically by name. If you have another definition, use that for the order by for row_number().

Upvotes: 5

Related Questions