There are proprietary solutions to this problem. Oracle, for example, provides a ROWNUM function which numbers the rows of a resultset. You posted this in an SQL Language forum, so an SQL language purist like myself would of course, avoid such proprietary features like the plague. :D
First off, when you say "...i want to select the first 20 records [sic] from a table...", what do you mean by 'first'? The concept of 'first' implies some ordering of the data. That is, that there is a way to determine which row is 'first', 'second', etc. The query you posted does not order the data at all, so the query processor is free to return the results in any order it chooses, and there is no guarantee the order would be the same each time it is run. Indeed, what basis did you use to place John and Jack in the second set of 20? The first set looked like it was alphabetic, but the second set proved that wrong. There is no way for me to determine how you grouped this data into sets of 20 rows.
Thus, although the TOP clause indeed restricts the resultset to 20 rows, you have no way to guarantee which 20 rows that is.
The simplest way to deal with your problem is to handle it in the client, since what you are really asking for is a resolution to a presentation issue, and such presentation issues are best left for the front end. RDBMS's such as Oracle and SQL Server are very good at storing and retrieving data, not so good at presenting it.
Another way is to save the results of your (ordered) query into a temporary table and include a sequence number column in the temporary table along with the rest of your query data. You can assign the sequence number in a variety of ways. In SQL Server, for example, this column could be an identity column which would automatically assign an increasing number to each row as it was inserted into the temporary table. Subsequent queries could then select ranges of rows based on this sequence number column.
The 'purist' way to number rows (not necessarily the best, as you'll see) is to partition the data into a series of subsets. Each subset contains all of the data rows whose sort value (remember that the rows
must be sorted for the concept of numbering to have any meaning) is less than the sort value of a given row. The 'sequence number' is then the count of the elements in that subset. A correlated subquery makes this (ahem) easy:
Code:
SELECT
(COUNT(*) FROM YourTable T2 WHERE T2.orderingcolumn<=T1.orderingcolumn) as seqno,
orderingcolumn, data1, data2, ...
FROM YourTable T1
ORDER BY orderingcolumn;
This assigns a 'sequence number' to each row, according to the 'orderingcolumn'. Note that this technique does not handle ties well. If the 'orderingcolumn' does not have unique values, then all the equal values of the 'orderingcolumn' will be assigned the same sequence number. Its straightforward to extend the technique to additional columns (like the primary key) to insure that the rows are ordered acording to some unique value or combination of values.
To select ranges of sequence numbers you just repeat the 'counting' subquery in the WHERE clause. Assuming the parameters '@Start' and '@End':
Code:
SELECT
(COUNT(*) FROM YourTable T2 WHERE T2.orderingcolumn<=T1.orderingcolumn) as seqno,
orderingcolumn, data1, data2,...
FROM YourTable T1
WHERE (COUNT(*) FROM YourTable T2 WHERE T2.orderingcolumn<=T1.orderingcolumn)
BETWEEN @Start AND @End
ORDER BY orderingcolumn;
This isn't going to be very fast if you have a large number of rows in your query. In that case, you are probably better off with the temporary table approach.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
SELECT TOP
