MySQL select distinct order by id
distinct, mysql, select, sql-order-by
Solution
By top 2, you seem to mean the ones at the end of the list. Try this:
select category
from t
group by category
order by max(id) desc
limit 2
If you mean the ones with the most rows.
Try this:
select category
from t
group by category
order by count(*) desc
limit 2
You can also include the `count(*)` in the select list to see what the counts are.
Problem
I have a table like the following: ``` id category 1 A 2 A 3 B 4 B 5 C 6 C ``` If I want to select the top 2 distinct categories ordered by id descending, what's the right query in mysql? I tried `select distinct category from table order by id desc limit 2` but that gave me the following result: ``` category C C ``` instead of ``` category C B ```