Select n largest values from a table

Question

How can I select the 100 largest rows in a table based on a column 'score'?

I can find the largest score in the 'score' column with:

SELECT max(score) FROM mTable

And then obtain that row(s):

SELECT * FROM mTable WHERE score=largestScore

But how would I wrap this up and obtain the following 99 lower scored rows?

Thanks.

score 10 · Accepted Answer · edited Aug 02 '10 at 01:13

10

Use:

SELECT t.*
FROM MTABLE t
ORDER BY t.score DESC
LIMIT 100

edited Aug 02 '10 at 01:13

Mitch Wheat

answered Aug 02 '10 at 01:04

OMG Ponies

Thanks. Should really have thought of doing it that way! – Jason Aug 02 '10 at 01:07
well, i did wonder if it was intentional, but it didn't look like any of the standard forms! – Mitch Wheat Aug 02 '10 at 01:49
@Mitch Wheat: I appreciate your honesty. Not much, but a little =) – OMG Ponies Aug 02 '10 at 01:52

score 2 · Answer 2 · edited Aug 02 '10 at 01:08

2

Formatted:

Select * 
 from mtable 
order by score desc  
limit 100

edited Aug 02 '10 at 01:08

OMG Ponies

answered Aug 02 '10 at 01:05

Tahbaza

score 1 · Answer 3 · answered Aug 02 '10 at 01:06

1

SELECT columnList
FROM mTable
ORDER BY score DESC
LIMIT 100

answered Aug 02 '10 at 01:06

Mitch Wheat

3 Answers3