Select TOP 20, Next 20, Next 20...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select TOP 20, Next 20, Next 20...

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-12-03 : 08:49:23

OK, I'm a little brain dead this morning. Consider this to get the top 20 rows...

select top 20 * from taskero order by TrackDataIDKey

How do I return the next 20 rows? (rows 21-40)
then the next 20 (rows 41-60)...
then the next 20 (rows 61-80)...
etc...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-03 : 08:51:56

select top 20 * from (select top 40 * from tbl order by fld) a order by fld desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-12-03 : 09:03:14

Thanks! I was forgetting the table name redirection (A before ORDER)

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-12-03 : 09:58:19

One more ? - Can TOP be paramterized?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-03 : 10:26:52

No, but ROWCOUNT can.
check out:
[url]http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx[/url]

rockmoose

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-12-03 : 10:29:14

Cool Name RockMoose! Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-03 : 11:52:57

Well, here's a sample of what not to do...

I thought FETCH ABSOLUTE might figure a way to jump pages....nut even the sample where you want to start at page 1 takes about 15 seconds..

The total is about 1/4 million rows...smaller tables fly though


USE Northwind
GO

CREATE PROC mySproc99 @Page int, @rows int
AS

SET NOCOUNT ON

DECLARE   @counter 		int
	, @spin_up		int
	, @OrderID		int
	, @CustomerID		nchar
	, @EmployeeID		int
	, @OrderDate		datetime
	, @RequiredDate		datetime
	, @ShippedDate		datetime
	, @ShipVia		int
	, @Freight		money
	, @ShipName		nvarchar
	, @ShipAddress		nvarchar
	, @ShipCity		nvarchar
	, @ShipRegion		nvarchar
	, @ShipPostalCode	nvarchar
	, @ShipCountry		nvarchar

DECLARE   @Orders 		table (
	  OrderID		int
	, CustomerID		nchar
	, EmployeeID		int
	, OrderDate		datetime
	, RequiredDate		datetime
	, ShippedDate		datetime
	, ShipVia		int
	, Freight		money
	, ShipName		nvarchar
	, ShipAddress		nvarchar
	, ShipCity		nvarchar
	, ShipRegion		nvarchar
	, ShipPostalCode	nvarchar
	, ShipCountry		nvarchar
)

SELECT @counter = 1, @Spin_Up = ((@Page*@Rows)-@Rows)+1


DECLARE myCursor99 CURSOR SCROLL
FOR
  SELECT  OrderID
	, CustomerID
	, EmployeeID
	, OrderDate
	, RequiredDate
	, ShippedDate
	, ShipVia
	, Freight
	, ShipName
	, ShipAddress
	, ShipCity
	, ShipRegion
	, ShipPostalCode
	, ShipCountry
  FROM Orders2
ORDER BY OrderID

  OPEN myCursor99

FETCH ABSOLUTE @Spin_up FROM myCursor99
INTO 	  @OrderID
	, @CustomerID
	, @EmployeeID
	, @OrderDate
	, @RequiredDate
	, @ShippedDate
	, @ShipVia
	, @Freight
	, @ShipName
	, @ShipAddress
	, @ShipCity
	, @ShipRegion
	, @ShipPostalCode
	, @ShipCountry
 
WHILE @@FETCH_STATUS = 0 AND @counter <= @rows
  BEGIN

	INSERT INTO @Orders (
		  OrderID
		, CustomerID
		, EmployeeID
		, OrderDate
		, RequiredDate
		, ShippedDate
		, ShipVia
		, Freight
		, ShipName
		, ShipAddress
		, ShipCity
		, ShipRegion
		, ShipPostalCode
		, ShipCountry)
	
	SELECT 	  @OrderID
		, @CustomerID
		, @EmployeeID
		, @OrderDate
		, @RequiredDate
		, @ShippedDate
		, @ShipVia
		, @Freight
		, @ShipName
		, @ShipAddress
		, @ShipCity
		, @ShipRegion
		, @ShipPostalCode
		, @ShipCountry
	
	FETCH NEXT FROM myCursor99
	INTO 	  @OrderID
		, @CustomerID
		, @EmployeeID
		, @OrderDate
		, @RequiredDate
		, @ShippedDate
		, @ShipVia
		, @Freight
		, @ShipName
		, @ShipAddress
		, @ShipCity
		, @ShipRegion
		, @ShipPostalCode
		, @ShipCountry

	SELECT @counter = @counter + 1
  END

CLOSE myCursor99
DEALLOCATE myCursor99

SELECT 	  OrderID
	, CustomerID
	, EmployeeID
	, OrderDate
	, RequiredDate
	, ShippedDate
	, ShipVia
	, Freight
	, ShipName
	, ShipAddress
	, ShipCity
	, ShipRegion
	, ShipPostalCode
	, ShipCountry
  FROM 	  @Orders

SET NOCOUNT OFF
GO


EXEC mySproc99 1,20


/*

DROP TABLE Orders2
DROP Proc mySproc99
GO

*/



/*
SELECT * INTO Orders2 FROM (
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL 
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL
SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders UNION ALL SELECT * FROM Orders
) AS XXX


CREATE INDEX Orders2_OrderId ON Orders2(OrderId)



*/

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-03 : 12:17:42

Cool Brett, never crossed my mind to use a cursor for this, but then I rarely think of cursor solutions.
Anyway,
If you just fetch the PK / UIX of the table into the @tvar then join the original table with @tvar
You would probably get a significant performance increase!

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources