Re: [sqlite] Sqlite question---->how to impose order on query
On Thu, Apr 10, 2008 at 09:56:31PM -0500, Dewey Gaedcke scratched on the wall: > Set @a = 0; > Select T1.rownumber, T1.Col2 from > ( > Select @a:[EMAIL PROTECTED] as rownumber, Col1, Col2 > from Table where Col1 = 'abc' Order by Col1 > ) as T1 > Where T1.rownumber between 21 and 30; As I understand it, most systems are going to do a full table scan for that sub-select. You're then using the outer select and the rownumber to essentially implement an LIMIT/OFFSET. If my assumptions about the full table scan are correct, this is extremely inefficient-- but if it works, it works. If you're getting the kind of performance you need, I think it would be much easier to just get rid of the outer select and put a LIMIT/OFFSET directly on the subselect. For example, the query above would turn into this: SELECT Col2 FROM Table WHERE Col1 = 'abc' ORDER BY Col1 LIMIT 10 OFFSET 20; This is still pretty inefficient when the offset starts to get large, but at least the query will terminate as soon as the limit is hit. I don't think that's the case for the sub-select you've got setup. So this is not the best, but it is still better than the original. (I also assume this is a contrived example, because as-written the ORDER BY clause is pointless thanks to the WHERE clause.) -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite question---->how to impose order on query
Dennis, Thanks for the info/link.if we must go this route (http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor), we will, but I'd much rather pay someone to write a simple embedded "sequence" function for us because we've got almost 10 (very complex) queries in mySQL that use the example in my earlier post (see below). We want to maintain the queries as close as possible to each other because the user needs to see the same results and not know the difference between running the query on the server or the (sqlite) client. Is it possible to find someone for hire to implement a "sequence" function so I don't have to rework all this query logic and distract my C++ programmer with the example implementation that you've provided?? Thanks again for responding!! Dewey We would replace "@a:[EMAIL PROTECTED]" in the query below with "f_sequence()" to generate a new relative rownum for each record found. Or more correctly, since the "Order by" execution phase of sqlite may not match mySQL, perhaps the function would best go in the outer query.in either case, is someone willing to build this for us: Query: Set @a = 0; Select T1.rownumber, T1.Col2 from ( Select @a:[EMAIL PROTECTED] as rownumber, Col1, Col2 from Table where Col1 = 'abc' Order by Col1 ) as T1 Where T1.rownumber between 21 and 30; No virus found in this outgoing message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.22.10/1367 - Release Date: 4/9/2008 7:10 AM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite question---->how to impose order on query result for paging
Dewey Gaedcke wrote: > > For example, if my query returns 80 rows, I'd like an extra column holding > sequential values 1 to 80 to help with paging > Check out this information page http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor It explains how to do paging efficiently in SQLite. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite question---->how to impose order on query result for paging
Hello, I hope this is the right place to get sqlite help. I'm trying to figure out how to generate a relative row # in a sqlite query result>(in native SQL if possible---I'm not a C++ guy). For example, if my query returns 80 rows, I'd like an extra column holding sequential values 1 to 80 to help with paging Suppose each page displays 10 records, to get page 3, in mySQL, I can use the @a session variable with a materialized view like this: Set @a = 0; Select T1.rownumber, T1.Col2 from ( Select @a:[EMAIL PROTECTED] as rownumber, Col1, Col2 from Table where Col1 = 'abc' Order by Col1 ) as T1 Where T1.rownumber between 21 and 30; -- gets only records for page 3 If this is not possible via the sql syntax, has someone written (or willing to write) a "sequence" function that would do the same thing so we don't have to do it ourselves in C?? Thanks in advance for any help, Dewey No virus found in this outgoing message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.22.10/1367 - Release Date: 4/9/2008 7:10 AM No virus found in this outgoing message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.22.10/1367 - Release Date: 4/9/2008 7:10 AM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
