Sqlite3 order by on integer not working (even with index)

Question

Hy,

can someone explain me this?

sqlite> .schema LastFM
CREATE TABLE LastFM(id INTEGER PRIMARY KEY, AlbumTitle TEXT, Hoerer INTEGER);
CREATE INDEX Hoerer_Index ON LastFM( Hoerer DESC );
sqlite> Explain SELECT * FROM LastFM ORDER BY Hoerer;
addr        opcode      p1          p2          p3          p4          p5
----------  ----------  ----------  ----------  ----------  ----------  ---------
0           Trace       0           0           0                       00
1           Noop        0           0           0                       00
2           Goto        0           16          0                       00
3           OpenRead    0           6           0           3           00
4           OpenRead    2           9           0           keyinfo(1,  00
5           Rewind      2           13          1           0           00
6           IdxRowid    2           1           0                       00
7           Seek        0           1           0                       00
8           IdxRowid    2           2           0                       00
9           Column      0           1           3                       00
10          Column      2           0           4                       00
11          ResultRow   2           3           0                       00
12          Next        2           6           0                       00
13          Close       0           0           0                       00
14          Close       2           0           0                       00
15          Halt        0           0           0                       00
16          Transactio  0           0           0                       00
17          VerifyCook  0           4           0                       00
18          TableLock   0           6           0           LastFM      00
19          Goto        0           3           0                       00
sqlite> SELECT * FROM LastFM ORDER BY Hoerer;
id          AlbumTitle    Hoerer
----------  ------------  ----------
35          Live In Oslo  1
36          Kleine Welt   1
37          The Land Of   1
38          No Limit / R  1
39          Faust... In   1
40          Live At Last  1
41          2009-10-10:   1
42          Collectif Me  1
13          Bbc Sessions  128
14          From Glory T  128
3           So Far        1294
27          Schiphorst 2  15
28          Od Serca Do   15
12          Faust / So F  178
32          The Wumme Ye  2
33          The Faust Co  2

I have an table LastFm(id INTEGER PRIMARY KEY, AlbumTitle TEXT, Hoerer INTEGER) and filled it with data in python from an csv file:

    reader = csv.reader(open(csvFileName, 'r'), delimiter=';')
    for row in reader:
        to_db = []
        for col in row:
            to_db.append(unicode(col, "utf8"))
        cur.execute("INSERT INTO LastFM(AlbumTitle, Hoerer) VALUES (?, ?);", to_db)
    sqlConnection.commit()

But the "order by" functionality is not working properly. I ve already tried to create an Index on the column i wannt to order but it is also not workin.

Some hints?

The problem is that you somehow managed it to insert the data as TEXT or BLOB. While TEXT should be convertet to an INTEGER if possible, BLOB can not converted. As short term fix I you could order it by CAST(Hoerer AS INTEGER) — Johannes Kuhn
What is the output of SELECT sql FROM sqlite_master WHERE name = "LastFM" and type = "table"? — Johannes Kuhn
sqlite> SELECT sql FROM sqlite_master WHERE name = "LastFM" and type = "table"; sql --- CREATE TABLE LastFM(id INTEGER PRIMARY KEY, AlbumTitle TEXT, Hoerer INTEGER) — user1911091
I do not understand. I ve defined the table column Hoerer as Integer. That s why i thought everything i add to the Hoerer Column is hold as INTEGER. And if it can not be converted to an INTEGER i will get an error. So what would be the right way to insert the data into the table if the csv - File data looks like: "Some text"; 123 — user1911091

CL. CL. · Accepted Answer · 2013-06-14T09:43:44

You have inserted strings into the Hoerer column, and that is how strings are ordered.

If you want to have numbers in this column, the import code has to insert numbers.

Sqlite3 order by on integer not working (even with index)

1 Answers