CODEWITHSUNDEEP
mysqlsql
Alec Rabold
Alec Rabold

Reputation: 3

ORDER BY number of similar values in a column

I have a list:

Class  |   Time    |   Days   |
AE200     800-950       MWF
AE200    1000-1150      MWF
CS107     800-950       MWF
CS107    900-1050       TTH
CS107    1100-1150      MWF
CS107    1100-1250      TTH
CS107    1100-1150      MWF
ME202    1400-1550      TTH

I'd like the Result Set to have the most commonly occurring classes on top, but NOT grouped together. The other columns' order (Time and Days) is irrelevant. It look like this:

Class  |   Time    |   Days   |
CS107     800-950       MWF
CS107    900-1050       TTH
CS107    1100-1150      MWF
CS107    1100-1250      TTH
CS107    1100-1150      MWF
AE200     800-950       MWF
AE200    1000-1150      MWF
ME202    1400-1550      TTH

How could I go about this?

I've tried ORDER BY Class ; and GROUP BY Class ORDER BY COUNT but I can't seem to get what I'm looking for. When I group the classes, the resultset only contains one of each class.

Upvotes: 0

Views: 36

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

An interesting way to do this uses a subquery in the order by clause:

select t.*
from t
order by (select count(*) from t t2 where t2.class = t.class);

Depending on the circumstances, this could be faster or slower than doing an explicit group by with a join.

An explanation. The GROUP BY should be faster when groups have many elements and there is no WHERE clause.

The correlated subquery could be faster under when there is an index on t(class) and the data is reduced (say, by a WHERE clause). Consider two edge cases:

  • If all the data is in one group, the the join and group by should be faster.
  • If a WHERE clause reduces the output to only one row and it is the only row in the group, then the correlated subquery is faster. The subquery is only called on one row.

The exact boundary between these varies. But, MySQL is much more likely to use an index for a correlated subquery than for a GROUP BY.

Upvotes: 1

Jorge Campos
Jorge Campos

Reputation: 23381

You have to know with one of then most commonly occurring by counting it, but since you cant group then you should do it with a subquery like this:

SELECT Class, Time, Days
  FROM yourTable a
         INNER JOIN
           (SELECT Class, count(*) as ord
              FROM yourTable
             GROUP BY Class) b 
         ON a.Class = b.Class
  ORDER BY b.ord DESC

Upvotes: 1

Related Questions