SQL syntax not supported in SQLite

Today it's natural to use top when writing SQL statements, I didn't find the problem at first, because I read the value from the database is exactly 0, and I am accustomed to variable definition of the time is also assigned a value of 0, but I do not 0 when I found the problem. Later only to know that the cute little sqlite unexpectedly has an unsupported SQL syntax.

See a forum a novice also found this problem and posted, the following a bull's reply is "Top is the key word?" SQLite home does not have this term, ha, originally top this word is in MSSQL only ~

Finally, I would like to say that the database is absolutely an important piece. My basic grammar is very rubbish, in a few days carefully recorded.

Results under Google:

1 TOP
This is a frequently asked question, such as the following statements can be used in SQL Server to get the first 10 records in a recordset:
SELECT TOP Ten * from [index] ORDER by IndexID DESC;
However, this SQL statement is not executable in SQLite and should be changed to:
SELECT * FROM [index] ORDER by IndexID DESC limit 0, 10;
Where limit 0,10 means starting from the No. 0 record, reading 10 entries

2 Creating a view (CREATE VIEW)
SQLite has a bug when creating a multi-table view, with the following issues:
CREATE VIEW Watch_single as SELECT Distinctwatch_item. [Watchid],watch_item. [Itemid] from Watch_item;
The above SQL statement will show success after execution, but in fact
SELECT COUNT (*) from [Watch_single] WHERE Watch_ single.watchid = 1;
No other statements can be executed outside of the executable. The reason for this is that the name of the table in which the field is located is specified when the view is established, and SQLite does not recognize it correctly. So the above creation statement should read:
CREATE VIEW Watch_single as SELECT DISTINCT [Watchid],[itemid] from Watch_item;
But the question that comes up is, what if it's a multi-table view with a rename field between the tables?

3 COUNT (DISTINCT column)
SQLite will error when executing the following statement:
SELECT COUNT (DISTINCT watchid) from [Watch_item] WHERE watch_item.watchid = 1;
The reason is that all of SQLite's built-in functions do not support distinct qualification, so there is some trouble if you want to count the number of records that are not duplicated. It is more feasible to first create a view of the record table that is not duplicated, and then count the view.

4 External connection
Although SQLite has officially claimed that the left OUTER join has been implemented, there is no right OUTER join and full OUTER join. But the actual tests show that it does not seem to work properly. The following three statements will be executed with an error:
SELECT tags. [TagID] from [Tags],[tag_rss] WHERE tags. [TagID] = Tag_rss. [TagID] (*);
SELECT tags. [TagID] from [Tags],[tag_rss], WHERE left OUTER JOIN tag_rss. [TagID] = tags. [TagID];
SELECT tags. [TagID] from [Tags],[tag_rss] WHERE the left JOIN Tag_rss. [TagID] = tags. [TagID];
It is also not feasible to use the + sign instead of the * number after testing.

Beyond C + + original articles, reproduced please indicate the source and keep the original link

This article link: http://www.beyondc.cn/sqlite-does-not-support-sql-syntax.html

SQL syntax not supported in SQLite