Top One by one
-
Hi All,
I have a list of 800000+ records. Now I want to see if someone selects 1 he will get top 10000 records again if he selects 2 then he will able to see next 10000 records (from 10,001 to 20,001) and so on....
How will I write this query?
-
I don't know how it performs on such a large dataset, but you can take a look at the NTILE() ranking function. A drawback is that the NTILE cannot be used in the WHERE clause, so you need to build it into a CTE. Something like:
;WITH CTE_SELECT AS(SELECT
NTILE(800000/10000) over (ORDER BY ID) AS tile
, *
FROM {table}
)
SELECT
*
FROM CTE_SELECT
WHERE tile = 1
There are several articles about your question. Search here in SQLServerCentral for articles containing "paging resultset" http://www.sqlservercentral.com/search/?q=paging+resultset&t=a
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
If you're using SQL Server 2012 you can use OFFSET..FETCH NEXT
DECLARE @PageNumber INT;SELECT *
FROM myTable
OFFSET (@PageNumber-1) * 10000 ROWS
FETCH NEXT 10000 ROWS ONLY;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi,
Thanks for your quick reply.
It will help me as a temporary solution. I am not getting exactly 10,000 records.
However Thanks for you reply
-
Hi Mark,
Thanks for your reply. FETCH option is good but I am using 2008R2. Here I will not get this option.
-
Instead of NTILE you could also apply a ROW_NUMBER. Add logic to your query to calculate the required rownumbers depending on your input variable (like: 1 = row 1 - 10000, 2 = row 10001 - 20000, 3 = row 20001 - 30000, etc). This will give you the exact number ow rows for each resultset.
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
When you apply a rownumber (inside the CTE), you can also get the desired rows by using a select TOP with a WHERE:
SELECT TOP (10000)
*
FROM table
WHERE rownumber > @int *10000
ORDER BY rownumber
Where @int in the above is the variable to determine the 'page' of the resultset...
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply