Q
Problem solve Get help with specific problems with your technologies, process and projects.

Paging with multiple columns

I have a table with a composite key having three or more columns. I want to fetch data in a bundle of 100. How can I include a composite key in my where clause?

I have a table with a composite key having three or more columns. I want to fetch data in a bundle of 100. How...

can I include a composite key in my where clause, or there is any other way to fetch the next bundle of data from the last row of data fetched in the previous bundle?

Your problem description suggests that you are trying to do paging. Please do a quick review of the article Paging through SQL query results (14 February 2005), specifically this part:

Another solution involves keeping track of the value of the sort column from the last time the query was run, so that you don't need a derived table:

select top N 
       col1, col2, sortcol
  from yourtable
 where sortcol > savedvalue       
order 
    by sortcol asc

Here, savedvalue is the value of the last sortcol from the previous execution. Of course, you need to modify the query on the first execution.

The reason that TOP is used in the above example is because this question arises most often from SQL Server users. As that other article mentioned, MySQL users would use LIMIT M,N and not require the WHERE clause with the saved values.

To make this work with three columns, just change the query slightly to this:

select top 100
       col1
     , col2
     , col3
     , data
  from yourtable 
 where col1 > col1value
    or (
       col1 = col1value
   and col2 > col2value
       )
    or (
       col1 = col1value
   and col2 = col2value
   and col3 > col3value
       )
order
    by col1
     , col2
     , col3

This query requires that we save three values from the previous execution, one for each column. Assuming that there is a composite index on these columns, the query should be very efficient.

But what if we're using a database that does not support TOP or LIMIT? We can use the solution mentioned in the article Paging through a result set without TOP or LIMIT (24 October 2002). Extending it to three columns, we have the following query for the second "bundle" of 100 rows:

select col1
     , col2
     , col3
     , data
  from yourtable as X
 where ( select count(*)
           from yourtable
          where col1 < X.col1
             or (
                col1 = X.col1
            and col2 < X.col2
                )
             or (
                col1 = X.col1
            and col2 = X.col2
            and col3 < X.col3
                )
        ) between 100 and 199
order
   by col1
    , col2
    , col3

Notice that the BETWEEN range used in this query is 100 through 199. These numbers refer to the number of rows that sort lower. The subquery uses COUNT(*) to count these lower rows. In the first "bundle" of 100 rows, the first (lowest) row has 0 rows lower, while the 100th row has 99 lower. So the second bundle of 100 rows, the 101st through 200th rows, have between 100 and 199 rows lower.

This was last published in October 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close