SQLite Forum

DESC keyword is ignored
Login

DESC keyword is ignored

(1) By anonymous on 2021-11-18 08:29:52 [link] [source]

Hi,

I created a sqlite file with a C# code, following an example from the internet. The code that creates the file is the following:

... SQLiteConnection.CreateFile(db_path); dbconnection = new SQLiteConnection("Data Source=" + db_path + ";Version=3;"); ...

Then, the table 'Mesures' is created, and some data entries are saved in the database.

In another part of the code, I need to get the latest 10 entries where attribute NumLot=Lot1, so I execute the following command:

SELECT * FROM Mesures WHERE NumLot IS 'lot1' ORDER BY 'Id' DESC LIMIT 10

My problem is that the 'DESC' keyword is ignored, and I always get the first 10 entries instead of the 10 latest ones.

Could you help me find the problem?

P.S.: I installed System.Data.SQLite version v1.0.115.5 with NuGet.

(2) By Stephan Beal (stephan) on 2021-11-18 09:46:05 in reply to 1 [source]

SELECT * FROM Mesures WHERE NumLot IS 'lot1' ORDER BY 'Id' DESC LIMIT 10

Try changing 'Id' (a string literal) to Id or "Id" or [Id] (an identifier).

(3) By anonymous on 2021-11-18 09:51:54 in reply to 2 [link] [source]

I can't believe it, it works! Thank you very much!

(4) By anonymous on 2021-11-24 09:17:22 in reply to 3 [link] [source]

I believe what is going is on is that ‘Id’ is a constant expression. So every row is being compared with each other for the the ordering to work.

In effect with a constant string : every row looks to be equal and therefore the ASC/DESC have no effect. (And probably it looks like ASC is working due to the (more or less) arbitrary ordering on “id” by default (i.e : it’s as if no ORDER BY clause is present at all)

Beware (this got me) - if you use a numeric - e.g “ORDER BY 2” - this will have an effect as I this case the numeric acts as an index for a column…but only as a “literal”. That is “ORDER BY 2” will order by the 2nd column - but “ORDER BY 1+1” will not. (That is a “constant” value 2).

(5) By Gunter Hick (gunter_hick) on 2021-11-24 11:52:04 in reply to 4 [link] [source]

From the docs:

"Each ORDER BY expression is processed as follows:

If the ORDER BY expression is a constant integer K then the expression is considered an alias for the K-th column of the result set (columns are numbered from left to right starting with 1).

If the ORDER BY expression is an identifier that corresponds to the alias of one of the output columns, then the expression is considered an alias for that column.

Otherwise, if the ORDER BY expression is any other expression, it is evaluated and the returned value used to order the output rows..."

and here:

"The SQL standard requires double-quotes around identifiers and single-quotes around string literals. ... But, in an effort to be compatible with MySQL 3.x ... SQLite will also interpret a double-quotes string as string literal if it does not match any valid identifier.

This misfeature means that a misspelled double-quoted identifier will be interpreted as a string literal, rather than generating an error. It also lures developers who are new to the SQL language into the bad habit of using double-quoted string literals when they really need to learn to use the correct single-quoted string literal form."