the stevie eXPerience 3.0

third time lucky!
posts - 31, comments - 26, trackbacks - 1

Ranged SQL Statement

Recently I was asked to write a SQL statement that would return the details of records between two positions. (e.g. all the records from the 11th record to the 20th record). Initially this seemed to be very simple. It was only when I went looking for the statement to do this that I realised it didn't exist (at least not that I'm aware of, I could be wrong and if I am please let me know so I can hang my head in shame!!)

To cut a long story short, I came up with the following statement

                SELECT
                TOP
                [intTo - intFrom + 1]
                 * 
                FROM
                 (    
                SELECT
                 * 
                FROM
                 some_table 
                WHERE
                 some_table.primary_key 
                NOT
                IN
                 (        
                SELECT
                TOP
                [intFrom - 1]
                 some_table.primary_key 
                FROM
                 some_table    )) 
                AS
                 dummy_table 
                ORDER
                BY
                 dummy_table.primary_key

In this example, intFrom is the position of first record you want returned, intTo is the position of the last record you want returned. For example, if you want all the records between position 21 and the 30 (i.e. intFrom=21, intTo=30), the statement would be:

                SELECT
                TOP
                 10 * 
                FROM
                 (    
                SELECT
                 * 
                FROM
                 some_table 
                WHERE
                 some_table.primary_key 
                NOT
                IN
                 (        
                SELECT
                TOP
                 20
                 some_table.primary_key 
                FROM
                 some_table    )) 
                AS
                 dummy_table 
                ORDER
                BY
                 dummy_table.primary_key

Anyways, I hope this helps someone!

posted on Wednesday, August 17, 2005 1:29 PM

Feedback

#   re: Ranged SQL Statement

Hey Stevie,

There is actually a way to do it - using LIMIT. You specify an offset (where 0 is the first record), and then how many rows you want to return. For example:

SELECT field1, field2, ..., fieldn FROM some_table
WHERE some_condition
LIMIT offset, numRows

so if you want the rows between position 21 and 30, you'd have

SELECT field1, field2, ..., fieldn FROM some_table
WHERE some_condition
LIMIT 20, 10

You can put the ORDER BY clause either before the LIMIT statment or after it, depending on whether you want to sort the results before getting the required number of rows, or not. It's also better to write the field names specifically instead of using select *, so you are absolutely certain that the fields will be returned in the order you expect, as it can vary.

Hope that helped! :)
8/22/2005 7:46 AM | Jenny

#   re: Ranged SQL Statement

I don't think that works with SQLServer 2000 though. I had tried using LIMIT, but I think it's an Oracle thing. Anybody know for sure?
8/22/2005 9:24 AM | Stevie

#   re: Ranged SQL Statement

I use MySQL, not Oracle... maybe it is dependent on what you're using.
8/22/2005 10:50 AM | Jenny
Post a new comment about this topic
Title  
Name  
Url

Comments  
Enter the code you see: