Unexpected behavior of ORDER BY and LIMIT in SQLite3 query

Question

I'm trying to return the records with the top 10 values in the field funny in my sqlite3 database. I used the following query:

SELECT * FROM ngrams
ORDER BY funny DESC
LIMIT 10;

This returns 10 records sorted in descending order by the funny field. However, the results appear to be only the top ten records for which funny is < 10. This is not what I wanted, and I'm not sure how I've gotten this wrong. For the record, the query below returns ~3,700, so these are certainly not the top ten records I wanted.

SELECT Count(*) FROM ngrams
WHERE funny > 10;

How can I return the records with the top ten funny values?

Column names are ngram, funny, useful, cool, ngcount.

Output:

sqlite> SELECT * FROM ngrams ORDER BY funny DESC LIMIT 10;
jetta| 9.914530| 5.726496| 6.367521| 117
emblem| 9.900000| 3.800000| 4.300000| 10
bt's| 9.875000| 11.375000| 10.625000| 8
dear phoenix| 9.857143| 8.285714| 7.857143| 7
look alikes| 9.857143| 7.428571| 6.428571| 7
alikes| 9.857143| 7.428571| 6.428571| 7
years im| 9.833333| 9.500000| 10.000000| 6
waaa| 9.833333| 8.000000| 7.000000| 6
earth do| 9.833333| 5.833333| 6.166667| 6
still full i| 9.833333| 7.500000| 8.000000| 6
sqlite> SELECT Count(*) FROM ngrams WHERE funny > 10;
3718
sqlite> SELECT * FROM ngrams WHERE ngram = 'megaphone';
megaphone| 14.777778| 10.555556| 10.555556| 9
sqlite> SELECT * FROM (
   ...> SELECT * FROM ngrams
   ...> ORDER BY funny DESC
   ...> ) topfunny LIMIT 10;
jetta| 9.914530| 5.726496| 6.367521| 117
emblem| 9.900000| 3.800000| 4.300000| 10
bt's| 9.875000| 11.375000| 10.625000| 8
dear phoenix| 9.857143| 8.285714| 7.857143| 7
look alikes| 9.857143| 7.428571| 6.428571| 7
alikes| 9.857143| 7.428571| 6.428571| 7
years im| 9.833333| 9.500000| 10.000000| 6
waaa| 9.833333| 8.000000| 7.000000| 6
earth do| 9.833333| 5.833333| 6.166667| 6
still full i| 9.833333| 7.500000| 8.000000| 6
sqlite>

Answer 1

Your funny column is TEXT. Given this:

create table ngrams (ngram text, funny text);
insert into ngrams (ngram, funny) values ('jetta', '9.914530');
insert into ngrams (ngram, funny) values ('emblem', '9.900000');
insert into ngrams (ngram, funny) values ('bt''s', '9.875000');
insert into ngrams (ngram, funny) values ('dear phoenix', '9.857143');
insert into ngrams (ngram, funny) values ('look alikes', '9.857143');
insert into ngrams (ngram, funny) values ('alikes', '9.857143');
insert into ngrams (ngram, funny) values ('years im', '9.833333');
insert into ngrams (ngram, funny) values ('waaa', '9.833333');
insert into ngrams (ngram, funny) values ('earth do', '9.833333');
insert into ngrams (ngram, funny) values ('still full i', '9.833333');
insert into ngrams (ngram, funny) values ('megaphone', '14.777778');

I get this output (columnized for readability):

sqlite> select * from ngrams order by funny desc;
ngram       |funny
jetta       |9.914530
emblem      |9.900000
bt's        |9.875000
dear phoenix|9.857143
look alikes |9.857143
alikes      |9.857143
years im    |9.833333
waaa        |9.833333
earth do    |9.833333
still full i|9.833333
megaphone   |14.777778

But if the funny values are numbers:

create table ngrams (ngram text, funny real);
insert into ngrams (ngram, funny) values ('jetta', 9.914530);
insert into ngrams (ngram, funny) values ('emblem', 9.900000);
insert into ngrams (ngram, funny) values ('bt''s', 9.875000);
insert into ngrams (ngram, funny) values ('dear phoenix', 9.857143);
insert into ngrams (ngram, funny) values ('look alikes', 9.857143);
insert into ngrams (ngram, funny) values ('alikes', 9.857143);
insert into ngrams (ngram, funny) values ('years im', 9.833333);
insert into ngrams (ngram, funny) values ('waaa', 9.833333);
insert into ngrams (ngram, funny) values ('earth do', 9.833333);
insert into ngrams (ngram, funny) values ('still full i', 9.833333);
insert into ngrams (ngram, funny) values ('megaphone', 14.777778);

Then the output is what you're expecting:

sqlite> select * from ngrams order by funny desc;
ngram       |funny
megaphone   |14.777778
jetta       |9.91453
emblem      |9.9
bt's        |9.875
dear phoenix|9.857143
look alikes |9.857143
alikes      |9.857143
years im    |9.833333
waaa        |9.833333
earth do    |9.833333
still full i|9.833333

SQLite's loose type system is both a blessing and curse, you must be very careful to make sure that you and SQLite agree on what type something is (both in the schema and the values you use). The trailing zeros in your output is a dead give away that something is up given that SQLite doesn't have fixed precision numeric or decimal types.

Answer 2

Try this:

SELECT * FROM (
    SELECT * FROM ngrams
    ORDER BY funny DESC
) topfunny LIMIT 10;