Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Top One by one Expand / Collapse
Author
Message
Posted Monday, July 1, 2013 4:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:33 AM
Points: 95, Visits: 170
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?
Post #1468987
Posted Monday, July 1, 2013 4:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:20 AM
Points: 2,218, Visits: 2,647
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’! **
Post #1468994
Posted Monday, July 1, 2013 6:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1469014
Posted Monday, July 1, 2013 6:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:33 AM
Points: 95, Visits: 170
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
Post #1469016
Posted Monday, July 1, 2013 6:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:33 AM
Points: 95, Visits: 170
Hi Mark,

Thanks for your reply. FETCH option is good but I am using 2008R2. Here I will not get this option.
Post #1469018
Posted Monday, July 1, 2013 6:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:20 AM
Points: 2,218, Visits: 2,647
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’! **
Post #1469020
Posted Monday, July 1, 2013 6:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:20 AM
Points: 2,218, Visits: 2,647
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’! **
Post #1469021
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse