How to get the top N values of each column in MySql

Question

I have a table with 50 columns and 1000s of rows. I want to output the top 5 records for each column. To get 1 record I do:

SELECT MAX(column1), MAX(column2), MAX(column3) FROM table

This gets the top value for each column but how can I get the second "max" value n times?

Answer 1

SELECT MAX(column1), MAX(column2), MAX(column3) FROM table LIMIT n

where n = number of times

however, you can only use LIMIT for the whole query, so you will need to pull second column separately.

Answer 2

There might be a nicer way, but you could just nest SELECT statements.

Something like:

SELECT (SELECT column1 FROM table ORDER BY column1 LIMIT 5) a,
       (SELECT column2 FROM table ORDER BY column2 LIMIT 5) b,
       (SELECT column3 FROM table ORDER BY column3 LIMIT 5) c

add "FROM dual" if you are using Oracle.

Answer 3

Assuming the greatest five values in each column could occur on distinct rows, you have to do this in 50 queries.

Explanation: the expressions in the select-list of an SQL query must refer to the same row. So you could get the top five values of column1 this way:

SELECT column1, column2, column3 FROM table ORDER BY column1 DESC LIMIT 5;

But of course the values of column2 and column3 wouldn't necessarily be the greatest values in those columns, they'd be whatever values happen to be on the same row with the greatest values in column1.

The only way to get values from different rows of the table into a single select-list is to do a self-join:

SET @i1 = 0, @i2 = 0, @i3 = 0, @i4 = 0, @i5 = 0;
SELECT * 
FROM (SELECT @i1:=@i1+1 AS i, column1 FROM table ORDER BY column1 DESC LIMIT 5) t1 ON (t1.i = t5.i)
JOIN (SELECT @i2:=@i2+1 AS i, column2 FROM table ORDER BY column2 DESC LIMIT 5) t2 ON (t1.i = t5.i)
JOIN (SELECT @i3:=@i3+1 AS i, column3 FROM table ORDER BY column3 DESC LIMIT 5) t3 ON (t1.i = t5.i)
JOIN (SELECT @i4:=@i4+1 AS i, column4 FROM table ORDER BY column4 DESC LIMIT 5) t4 ON (t1.i = t5.i)
JOIN (SELECT @i5:=@i5+1 AS i, column5 FROM table ORDER BY column5 DESC LIMIT 5) t5 ON (t1.i = t5.i);

But this isn't practical or efficient for 50 columns.

I also have to wonder about your database design. If you need the top five from 50 columns, could it be that they're all storing the same kind of information? You could be practicing the Multi-Column Attributes antipattern. If so, you should put all 50 columns into a single column in a child table.

Answer 4

This is NOT something you want to do in a single query. Just break it out, one query for each column. Under the right circumstances (aka with the right indexes and right column types), MySQL can actually optimize these queries by short circuiting so that it never has to scan the whole table, it just pulls out the top 5 values and done.

SELECT column1 FROM table ORDER BY column1 DESC LIMIT 5
SELECT column2 FROM table ORDER BY column2 DESC LIMIT 5
etc

If you try to smoosh them all together into one giant, kludgy query you'll only manage to convince the optimizer to just give up and rescan the whole table 50 times and then use 50 temp tables and probably some file sorting for good measure. So unless your table has about 10 rows in it (which it obviously doesn't), 50 separate queries will always be faster.