Jump to content

SQLite - is order by selection possible?


 Share

Recommended Posts

I have multiply words SQLite selection query like this:

$sSql_Query = "SELECT FullFilePath FROM DB WHERE FileName LIKE '%" & $Search_1 & "%' OR FileName LIKE '%" & $Search_2 & "%' OR FileName LIKE '%" & $Search_3 & " ORDER BY FileName"

It returned in "ORDER BY FileName", but is it possible to have return in order by how is search sentence is built - first $Search_1, then $Search_2 and then $Search_3 (and so on up to end of current query)?

Link to comment
Share on other sites

Use UNION

 

like this

$sSql_Query = "SELECT FullFilePath FROM DB WHERE FileName LIKE '%" & $Search_1 & "%' UNION SELECT FullFilePath FROM DB WHERE FileName LIKE '%" & $Search_2 & "%' UNION SELECT FullFilePath FROM DB WHERE FileName LIKE '%" & $Search_3

 

Edited by Zedna
Link to comment
Share on other sites

When no order by clause is specified, SQL engines are free to return results in any order of their choice. Most of the times that means the simple order which you expect, namely the order resulting from inserts/deletes/replaces but one should never rely on this.

You can achieve the order you want by unioning resuts along with a select number, like this:

$sSql_Query = _
"select ffp from (" & _
"   SELECT 1 rank, filename, FullFilePath ffp FROM DB WHERE FileName LIKE '%" & $Search_1 & "%' " & _
"   union all " & _
"   SELECT 2 rank, filename, FullFilePath ffp FROM DB WHERE FileName LIKE '%" & $Search_2 & "%' " & _
"   union all " & _
"   SELECT 3 rank, filename, FullFilePath ffp FROM DB WHERE FileName LIKE '%" & $Search_3 & "%' " & _
") " & _
"ORDER BY rank, FileName"

 

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

I don't believe any index would help and here is why.

The 3 sub-queries all need a full table scan since you search for substrings which can be in the middle of the searched column. SQLite query planner has an optimization for searches where the LIKE argument is a prefix of the string (e.g. "select this, that from mytable where column like 'abc%'"). Obviously no index can be used when the argument is '%abc%'.

Now comes the outer select: it needs to scan the temporary table created by the union of the 3 sub-queries, and this temp table doesn't have an index automagically created. Furthermore, the average use case is when the result set is relatively small but in such case and even if the query planner would be smart enough to build an index on rank, filename, that would take longer than just sorting the rows on the very same criterion: rank then filename.

Issuing a statement to increase SQLite cache size (default to 2000 pages) could possibly help, making full use of the cache created by the first sub-query in the subsequent other 2:

_SQLite_Exec($hDB, "PRAGMA cache_size = 20000;")

This statement is only persistent over the current connection.
 

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...