Need Sql Query

[phprockz]

Hi there,
Can any one tell query for selecting records starting from 4th record out of 10 records.

thanks

[txbakers]

select * from table limit 4, 10000000

[piatkow]

Another candidate for dbdebunk.

You may be using a database which lets you access rows in a table by a squence number but if you are asking generically about SQL it makes no sense. Rows have keys, positions are an implemenation issue which, in a properly designed dbms, is totally transparrent to queries.

[coopster]

Welcome to WebmasterWorld, piatkow.

Although I might agree in theory I would disagree in practice. Have you ever written a pagination function for a database that does not offer some form of offset and/or limit clause? Consider the following:

SELECT LastName, FirstName, MiddleName, IdentificationNumber FROM myTable ORDER BY LastName, FirstName, MiddleName, IdentificationNumber;

Now you want to display only the first 10 rows. OK, so you fetch only the first ten rows and free your result set. Display your data and wait for user input. Now your user wants to page forward. How do you handle it? With a logical WHERE clause, correct?

SELECT 
  LastName, 
  FirstName, 
  MiddleName, 
  IdentificationNumber 
 FROM myTable 
 WHERE 
  ( 
   LastName > 'Last-LastNameDisplayed' 
   OR 
   ( 
     LastName = 'Last-LastNameDisplayed' 
     AND 
     ( 
       FirstName > 'Last-FirstNameDisplayed' 
       OR 
       ( 
        FirstName = 'Last-FirstNameDisplayed' 
        AND 
        ( 
          MiddleName > 'Last-MiddleNameDisplayed' 
          OR 
          ( 
           MiddleName = 'Last-MiddleNameDisplayed' 
           AND 
           IdentificationNumber > 123456 
          ) 
        ) 
       ) 
     ) 
   ) 
  ) 
;

It gets much messier when you the compound key is more extensive. The ability to use an offset and a limit definitely has it's advantages.

[txbakers]

The ability to use an offset and a limit definitely has it's advantages.

Exactly. Why code lots of lines, when you can just capture the page number from the link, and set your offset to X times the page number?

One secret to effective SQL programming (and programming in general I think) is write CLEAN code.

[syber]

The ANSI standard way of doing this is:

SELECT * 
 FROM products 
 WHERE ProductID > (SELECT MIN(ProductID)  
      FROM products 
      WHERE ProductID > (SELECT MIN  
      (ProductID) FROM products  
       WHERE ProductID >  
       (SELECT MIN(ProductID)  
        FROM products)))