CODEWITHSUNDEEP
mysqlsql
KinsDotNet
KinsDotNet

Reputation: 1560

Selecting values which appear most frequently in order of frequency

I have a column from which I would like to return only distinct values, not matching certain criteria, in descending order of the most frequent value.

The column contains these records:

this 
those
that
dog
these
here
there
cat
dog
hamster
hamster
there
blah
here
blah
blah
dog

So I have:

SELECT DISTINCT(rcolumn)
FROM otable
WHERE reason != 'this'
AND reason != 'that'
AND reason != 'those'
AND reason != 'these'
AND reason != 'them'
AND reason != 'here'
AND reason != 'there'
AND reason != 'in between'
AND reason != 'all over'
AND reason != 'something'
AND reason != 'something else'
AND reason != 'anywhere'
AND reason != 'anywhere else'

Which will return:

blah
cats
hamsters
dogs

But I would like it to return:

blah
dogs
hamsters
cats

In that order, as blah appears most in the table, then dogs, then hamsters, then cats.

Upvotes: 0

Views: 68

Answers (2)

Sudec
Sudec

Reputation: 124

Just simply add ORDER BY COUNT(rcolumn) DESC at the end of your statement 

SELECT DISTINCT rcolumn
FROM otable
WHERE reason != 'this'
AND reason != 'that'
AND reason != 'those'
AND reason != 'these'
AND reason != 'them'
AND reason != 'here'
AND reason != 'there'
AND reason != 'in between'
AND reason != 'all over'
AND reason != 'something'
AND reason != 'something else'
AND reason != 'anywhere'
AND reason != 'anywhere else'
ORDER BY COUNT(rcolumn) DESC
;

Upvotes: 0

simpleigh
simpleigh

Reputation: 2894

In order to return stuff in order of frequency you'll need to GROUP BY the values:

SELECT   rcolumn
FROM     otable
-- WHERE stuff
GROUP BY rcolumn;

This should give basically the same answer as what you have. You can then easily count the incidence of each row and sort:

SELECT   rcolumn, COUNT(*) AS frequency
FROM     otable
GROUP BY rcolumn
ORDER BY frequency DESC;

If you'd prefer not to see the calculated frequency column in the results then you can just order by this straight away:

SELECT   rcolumn
FROM     otable
GROUP BY rcolumn
ORDER BY COUNT(*) DESC;

In general when using a GROUP BY clause be careful to only select either:

  • columns you're grouping by
  • results of aggregate functions

Most RDBMSes will catch this as an error, but MySQL will just pick a random row and return you that data.

Finally for added clarity, why not change your long WHERE clause into something more elegant:

WHERE rcolumn NOT IN
    ('this'
    ,'that'
    ,'those'
    -- ...
)

Upvotes: 1

Related Questions