SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Top One by one


Top One by one

Author
Message
niladri.primalink
niladri.primalink
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 226
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?
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4454 Visits: 3672
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’! **
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3161 Visits: 24128
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




niladri.primalink
niladri.primalink
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 226
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
niladri.primalink
niladri.primalink
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 226
Hi Mark,

Thanks for your reply. FETCH option is good but I am using 2008R2. Here I will not get this option.
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4454 Visits: 3672
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’! **
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4454 Visits: 3672
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’! **
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search