faustf

sql select command find row number 8

16 posts in this topic

hi guy   i  have    db in sqlite   like  this  

ID          NAME        SALARY
----------  ----------  ----------
1           Paul        20000.0
2           Allen       15000.0
3           Teddy       20000.0
4           Mark        65000.0
5           David       85000.0
6           Kim         45000.0
7           James       10000.0

if  i wanna  extract  only  a row   4   how  is possible? ?? 

 

thankz at all 

 

Share this post


Link to post
Share on other sites



i  answer me  sorry  ,  SELECT * FROM ps_country LIMIT 1 OFFSET 7  i think is  correct?? 

 

Share this post


Link to post
Share on other sites

Use a suitable WHERE clause: SELECT * FROM ps_country where id = 4

Why do you think OFFSET 7 is OK to get row with ID=4?


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)

Share this post


Link to post
Share on other sites

yea yea  i  do erro  for number  , but  concept  is  correct   yea  if  find  a  4  row  you must insert  3   SELECT * FROM ps_country LIMIT 1 OFFSET 3

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Using OFFSET xxx clause skips xxx rows in the resulting resultset, but the issue is that you have no idea about the order of the resultset returned by SQLite (or any SQL engine by the way). You see, SQL operates on sets (well, something very close to mathematical sets) and sets are unordered. So are resultsets from select queries, unless you specify an order by clause. Then yes, you can rely on OFFSET X LIMIT Y to return the rows X+1 to X+Y in the ordering you specify. In absence of an order by clause, you should consider output as being randomly sorted, where offset is essentially a no-op.

Use this pragma to check that your queries and application(s) don't rely on unreliable assumptions:

PRAGMA reverse_unordered_selects = boolean;

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)

Share this post


Link to post
Share on other sites

but how  order by default  when  you call select *  from table? 

 

Share this post


Link to post
Share on other sites

There is NO default. A table is a set, not an ordered set. YOU have to specify an order by clause to force a sort of the resultset, else it can be as random as the engine finds it easier to make it.

select <columns> from mytable where <condition> order by <this or that> offset x limit y


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)

Share this post


Link to post
Share on other sites

also mysql and postgres???  do this?? 

 

Share this post


Link to post
Share on other sites

Yep, unfortunately many SQL statements rely on untold (and wrong) assumptions about how the engine actually behave.

It's not that SQL engines try hard to fool you, just that this is how SQL works under the hood. In practice you can often believe that the order by clause is optional since if you try you can think that the output order is always the same and is what you would expect, but any engine is free to deliver the same resultset in completely different orders on successive invokations of the same query. This is an implementation detail and noone is supposed to rely on implementation details for code/result correctness.

1 person likes this

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)

Share this post


Link to post
Share on other sites

a  ok i  think  is  best  if  i order  always  , :)   thankz  so much  for  suggest 

 

Share this post


Link to post
Share on other sites

As always, better safe than sorry. Please note that this is completely distinct from any index you can have. Creating an index only serves to speedup searches in a specific order but doesn't imply a similar order (nor any particular order) in the output resultset. So it's safe to assume a random order in the absence of an order by clause.

Operations are in this sequence (in the simplest case):

1/ the SQL is parsed and optimized for using the most useful index, if any
2/ the resultset is formed, filtered by the WHERE clause
3/ the resultset from 2/ is sorted according to the ORDER BY clause, if present
4/ the first X rows are skipped according to the OFFSET X clause, or 0 by default
5/ Y rows are output according to LIMIT Y clause, or ALL remaining rows if clause not present

1 person likes this

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)

Share this post


Link to post
Share on other sites

understund :D  i think :)

Share this post


Link to post
Share on other sites

Standard interview SQL question...how do you get the 2nd highest salary from a table?

I'd get the top 2 ids by salary descending, and then of those ids, select the top 1 asc.

select top 1 * from salary where id in (select top 2 id from salary order by salary desc) order by salary asc

Same principle works for any number...if you want the 4th highest: select top 5 descending, and then of those ids, select the top 1 asc.

Just throwing that out there, because I don't see what the issue is when you can get the row by ID=4...right?


IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites
3 hours ago, jdelaney said:

select top 1 * from salary where id in (select top 2 id from salary order by salary desc) order by salary asc

AFAICT I doubt every SQL engine would actually apply an order clause to an inner subquery, but  even if, why do that in two steps?

select * from salaries order by salary desc offset 1 limit 1


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)

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

I'd think that an inner sort would be more prominent than offset...but I only base that on the keywords being so fundamental.

Edited by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

Assume a table with 1 million rows. Your subquery has to sort 1 million rows and pick up the top 2, then sort those 2 and pick up 1 result.

The single query sorts 1 million rows skips 1 and returns the next.


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)

Share this post


Link to post
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