CODEWITHSUNDEEP
mysqlsqlselect
dshukertjr
dshukertjr

Reputation: 18612

Ordering values from most to least in SQL

I want to retrieve the table value in order of from most to least. For example, if we had a single column table like the following,

selected_val
    2
    3
    3
    2
    1
    3
    1
    1
    1
    4

I need a SQL that will return the values in the order of 1, 3, 2, 4, because there are four 1's, three 3's, two 2's, and one 4 in the table. I only need distinct values, which means I don't want 1,1,1,1,3,3,3,2,2,4. Is this possible?

Upvotes: 2

Views: 4012

Answers (2)

Mureinik
Mureinik

Reputation: 311163

You could group by the value and order by count(*):

SELECT   selected_val
FROM     mytable
GROUP BY selected_val
ORDER BY COUNT(*) DESC

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use grouping to achieve this:

SELECT selected_val
FROM mytable
GROUP BY selected_val
ORDER BY COUNT(*) DESC

The trick here is to ORDER BY the row count of each group: descending ordering yields the selected_val having the greatest row count in the first place, then comes the second value, etc.

Upvotes: 4

Related Questions