March 9, 2015 at 8:53 am
Select top 100 * from tab1 order by col1 ---gives me the top 100 order by col1
Select top 200 * from tab1 order by col1 ---gives me the top 200 order by col1
How do I select the top 100 to 200
March 9, 2015 at 9:15 am
mw112009 (3/9/2015)
Select top 100 * from tab1 order by col1 ---gives me the top 100 order by col1
Select top 200 * from tab1 order by col1 ---gives me the top 200 order by col1
How do I select the top 100 to 200
with cte_id as
(
select
Rowno = row_number() over (order by col1)
,col1
,col2
,coln
from tab1
)
select
col1
,col2
,coln
from cte_id
where rowno >= 101
and rowno <= 200
That's one way of doing it.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
March 9, 2015 at 9:17 am
Sample data: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS randomDateTime,
DATEADD(DAY,((ABS(CHECKSUM(NEWID())) % 366 /*(Number of days in range)*/) + 1),CAST('1945' AS DATE) /*(Start date, e.g. '1945-01-01*/) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney,
CRYPT_GEN_RANDOM(1) % 2 AS randomBool
INTO #testEnvironment
FROM master.dbo.syscolumns sc1
CROSS JOIN master.dbo.syscolumns sc2
CROSS JOIN master.dbo.syscolumns sc3;
Using OFFSET: -
SELECT *
FROM [#testEnvironment]
ORDER BY [ID]
OFFSET 100 ROWS
FETCH NEXT 100 ROWS ONLY;
Using ROW_NUMBER: -
SELECT [a].[ID], [a].[randomDateTime], [a].[randomDate], [a].[randomBigInt], [a].[randomSmallInt], [a].[randomSmallDec],
[a].[randomTinyDec], [a].[randomBigDec], [a].[randomMoney], [a].[randomBool]
FROM (
SELECT [ID], [randomDateTime], [randomDate], [randomBigInt], [randomSmallInt], [randomSmallDec], [randomTinyDec],
[randomBigDec], [randomMoney], [randomBool], ROW_NUMBER() OVER ( ORDER BY [ID] ) AS RN
FROM [#testEnvironment]
) a
WHERE RN BETWEEN 101 AND 200;
Using nested tops: -
SELECT TOP 100
[a].[ID], [a].[randomDateTime], [a].[randomDate], [a].[randomBigInt], [a].[randomSmallInt], [a].[randomSmallDec],
[a].[randomTinyDec], [a].[randomBigDec], [a].[randomMoney], [a].[randomBool]
FROM (
SELECT TOP 200
[ID], [randomDateTime], [randomDate], [randomBigInt], [randomSmallInt], [randomSmallDec], [randomTinyDec],
[randomBigDec], [randomMoney], [randomBool]
FROM [#testEnvironment]
ORDER BY [ID]
) a
ORDER BY [ID] DESC;
etc etc.
March 9, 2015 at 6:28 pm
Cadavre (3/9/2015)
Using OFFSET: -
SELECT *FROM [#testEnvironment]
ORDER BY [ID]
OFFSET 100 ROWS
FETCH NEXT 100 ROWS ONLY;
+1. My first thought when I read the question.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply