August 31, 2007 at 1:21 pm
Hello
Is it possible in MSSQL 2005 to create a query where we define that we want 2000 register starting at the register 30000?
Using only top I'm stuck to read 32000 registers and avoid the first 30000, but when you want to use a control of visual studio that receives a dataset, then the problem is bigger
Thanks in advance for any answer.
August 31, 2007 at 1:42 pm
Where register between 30000 and 32000
will work for you ?
August 31, 2007 at 2:09 pm
I'm sorry for the misunderstanding, register is not a table, is not a column of any table
from a query that gives me a huge resultset, I want 2000 register, starting at offset 30000
Thanks for your attention
September 1, 2007 at 3:27 pm
SELECT TOP 2000 *
FROM Table1
WHERE ID >= 30000
ORDER BY ID
N 56°04'39.16"
E 12°55'05.25"
September 3, 2007 at 4:31 am
SELECT TOP 2000 *
FROM TABLE1
WHERE ID NOT IN (SELECT TOP 30000 ID FROM TABLE1 ORDER BY ID)
ORDER BY ID
September 3, 2007 at 6:43 am
If your table doesn't have an identity column to work with (or if you need to order by some other column for purposes of choosing the desired range of records to return), SQL Server 2005 offers another alternative - the row_number() function. This function will identify the row number of the record in the resultset and allow you to then filter out which rows you would like returned.
Here's an example:
WITH NumberedRows AS
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY Name) AS 'RowNumber'
FROM Master.dbo.SysDatabases
)
SELECT *
FROM NumberedRows
WHERE RowNumber BETWEEN 8 and 10;
September 4, 2007 at 5:44 am
If you're trying to implement paging (which it sounds like you're doing), have a look at: http://www.sqlservercentral.com/columnists/jSebastian/3181.asp
It has seveal links to ways to accomplish paging, including front and back end code.
September 4, 2007 at 9:11 am
To get the bottom using top, issue and ORDER BY Col1 DESC
The Descending will reverese the order and the top statement will grab the bottom.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply