Selecting next top n records....
-
I have table contains more than 4 lakhs records.
It has a "SerialNo" field containing row number but some rows has been deleted so this field is not sequential any more.
Now I want to fetch 50000 rows from that table next to the first 50000 rows in table in order to the field "SerialNo"
Please give some advice.
Thanks in advance
-
Hi Dinendra,
Have a look at the TOP keyword in SQL Server Books Online and also the LIMIT keyword. The TOP keyword allows you to select the TOP n rows from a table, the LIMIT keyword allows you to select the n rows starting at offset m in your table.
- James
--
James Moore
Red Gate Software Ltd -
Supposing that the last SerialNo read is 50165 the next 50000 statement is:
select top 50000 *
from table-name
where SerialNo > 50165
order by SerialNo
In case that u r in a SP, you can keep the last SerialNo in a variable, say @Last_SerialNo... and execute the query as
select top 50000 *
from
where SerialNo > @Last_SerialNo
order by SerialNo
I this case i suggest u to use a cursor.
------------
When you 've got a hammer, everything starts to look like a nail... -
Thanks Andreas.It really help me
-
James ,thank you for your information.
-
I don't believe the LIMIT keyword is part of SQL Server.
--Andrew
-
Heh, you are right Andrew,
my bad.. I obviously spent too long programming with MySQL
- James
--
James Moore
Red Gate Software Ltd -
Dinendra,
First, don't even think
cursor... especially not for this size table... 
The following is fully programmable for page size and page number and will return the first page of 100 in 13 to 30 milliseconds and the last page (#14000) of 100 in 580 to 690 milliseconds on a 1.4 million row, 78 column table... PROVIDED it is indexed correctly. The table I used has gaps as small as 1 and as large as 100,000 and the code suffers no misses. It will, however take about 6 seconds per page if the pages are 50,000 rows long (why ya wanna do that, anyway??)
Of course, you will need to change the table name and a column name to make it suit your purposes...
declare @start datetime
set @start = getdate()
--===== Declare the local variables
DECLARE @PageSize INT --How many rows to appear per page
DECLARE @PageNum INT --What page number to appear
DECLARE @Skip INT --Working variable for where to start for page
DECLARE @SQL VARCHAR(8000) --Holds dynamic SQL
--===== Set the local variables for pagesize and page
-- PageSize and PageNum would be parameters in a stored proc
SET @PageSize = 50000
SET @PageNum = 10
SET @Skip = @PageSize*@PageNum
--===== Create the dynamic SQL statement that will produce the page
SET @SQL =
'
SELECT c.*
FROM dbo.Customer c,
(
SELECT TOP '+STR(@PageSize)+ ' *
FROM dbo.Customer WITH (NOLOCK)
WHERE CustID NOT IN (SELECT TOP '+STR(@Skip)+' CustID
FROM dbo.Customer
ORDER BY CustID)
ORDER BY CustID
) d
WHERE c.CustID = d.CustID
ORDER BY d.CustID
'
--===== Produce the page
EXEC (@SQL)
print CONVERT(varchar(30),getdate()-@start,114)
Now, duck... here come the ORDER BY police...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You could possibly use the statement:
Set RowCount 50000
instead to stipulate the number of rows rerurned instead of the TOP 50000 statement.
Kindest Regards,Catherine Eibner
cybner.com.au
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply