is there a way in a query to specify by record number
-
example return records by number. or from record 10000 to 20000?
is there something similar to top for bottom or middle.
example
select top 10000 from Customers
thank you
-
Do you need this for some sort of paging functionality, or something? There are plenty of examples around that illustrate paging through resultsets.
--------------------
Colt 45 - the original point and click interface
-
To select, for example, the 4th "page" of 10,000 records you could use:
SELECT * FROM (
SELECT TOP 10000 * FROM (
SELECT TOP 50000 *
FROM Customers
ORDER BY CustomerID
) a
ORDER BY CustomerID DESC
) b
ORDER BY CustomerID
This will slow down as you get to larger tables and try requesting page 237.
I just finished working on a project where I needed to process large tables in 10,000-row chunks and wanted to get the key values for the first and last record of each chunk. Assumptions are that there is a single-column int primary key or unique index, and that the table is static with no insertions or deletions during the process.
CREATE TABLE #Keys (
ID int not null identity primary key clustered,
KeyValue int not null)
INSERT INTO #Keys (KeyValue)
SELECT [<pk>]
FROM <tbl> WITH(NOLOCK)
ORDER BY [<pk>]
SELECT (a.ID + 9999) / 10000 as Page,
a.KeyValue as MinKey,
ISNULL(b.KeyValue, c.KeyValue) as MaxKey,
ISNULL(b.ID, c.ID) - a.ID + 1 as Records
FROM #Keys a
LEFT JOIN #Keys b ON a.ID + 9999 = b.ID
CROSS JOIN (SELECT TOP 1 * FROM #Keys ORDER BY ID DESC) c
WHERE a.ID % 10000 = 1
ORDER BY a.ID
This worked for my project, but if someone has a better solution I'd be very interested.
-
Hi guys,
If your database is SQL Server, you can use the command "SET ROWCOUNT".
Also, this link will explain others tricks.
http://www.dpawson.co.uk/xsl/sect2/paging.html
Have fun !
-
Hi,
This is for ruturnin TOP 10
select top 10 customerid from Customers order by customerid
This is for returning Bottom 10
select top 10 customerid from Customers order by customerid desc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply