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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading