sql select command find row number 8

[faustf]

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 

 

[faustf]

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

 

[jchd]

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?

[faustf]

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

[jchd]

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 February 22, 2016 by jchd

[faustf]

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

 

[jchd]

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

[faustf]

also mysql and postgres???  do this?? 

 

[jchd]

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.

[faustf]

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

 

[jchd]

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

[faustf]

understund   i think

[jdelaney]

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?

[jchd]

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

[jdelaney]

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 February 23, 2016 by jdelaney

[jchd]

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.