MySQL – Get the Top 2 Rows for Each Category with Certain Condition

Suppose you have table TNILAI in MySQL which contains data like this:

id nama nilai

1 | A | 8 |
2 | B | 9 |
3 | C | 9 |
4 | A | 7 |
5 | B | 5 |
6 | C | 6 |
7 | A | 8 |
8 | B | 9 |
9 | C | 6 |

And you just want to get the top 2 rows for each ‘nama’ category, not including categories which have the value of ‘nilai’ < 6 and ordered by the latest id then the query is like this:

select * from
(select *
from tnilai n
where
( select count(*)
from tnilai m
where n.nama = m.nama
and n.id <= m.id) <= 2
order by n.nama, n.id desc) as tn
where nama not in
(select nama
from tnilai
group by nama
having min(nilai) < 6);

MySQL – Get the Top 2 Rows for Each Category with Certain Condition

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s