November 28, 2006 at 12:52 pm
Hi all, need a help on this query:
i have a query which says
select top 10 item_id from items
i need a second query now to select items from 11 to 20 how to do it?
Thanks,
Sankar
November 28, 2006 at 1:08 pm
That's one way of doing it... but I don't think it'll solve your problem :
USE pubs
DECLARE @var1 VARCHAR(12)
DECLARE @var2 VARCHAR(30)
SET ROWCOUNT 10
SELECT @var1 = au_id, @var2 = au_lname FROM authors ORDER BY au_lname, au_id
SET ROWCOUNT 0
SELECT TOP 10* FROM authors
WHERE (au_id >= @var1 AND au_lname=@var2) OR ( au_lname>@var2)
ORDER BY au_lname, au_id
If it doesn't help then can you post more details about the requirements?
November 28, 2006 at 1:16 pm
You can also get the results by using temp tables:
USE Pubs
Go
CREATE TABLE #OrderNum
(ord_Num VARCHAR (20))
INSERT #OrderNum
SELECT top 20 Ord_Num
FROM sales
SELECT TOP 10 ord_Num
FROM #OrderNum
SELECT Top 10 ord_Num
FROM #OrderNum
WHERE ord_Num NOT IN
(SELECT TOP 10 ord_Num
FROM #OrderNum)
DROP TABLE #OrderNum
November 28, 2006 at 1:27 pm
thanks both for the reply.
The query is used for generating a report using sql reporting services. I am using 2 databases to generate one report.
I have a query tht is running properly but due to the high volume of records returned, the rendering of report is too slow and time consuming.. so i planned to break the report into part1, part2 with the first report being generated for Top 4000 records and the next report from 4001 to the end of recordset.(this will be a new query with new report)
I am using between syntax to do the task. I figured manually how many records are returned.
I wanted to know if it could be done using just the TOP statement like instead of Top 4000, is there a way to say 4001 to 9000?
if not,i'd better go with the solution given or between syntax.
Thanks,
sankar
November 28, 2006 at 1:32 pm
This is the most complete answer I ever saw to this problem.
Let us know if you need more help :
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
November 29, 2006 at 9:44 am
Maybe something like this -- Create a tmp table with a unique identifier, populate the table, then loop through the tmp table based on the unique ID:
CREATE TABLE #tblName (
UniqueID INT IDENTITY,
varValue VARCHAR(50)
)
INSERT #tblName
SELECT fieldName AS varValue
FROM FirstDatabaseTable
WHERE whatever = 'whatever'
INSERT #tblName
SELECT fieldName AS varValue
FROM SecondDatabaseTable
WHERE whatever = 'whatever'
SELECT TOP 10
varValue
FROM #tblName
ORDER BY UniqueID
SELECT TOP 10
varValue
FROM #tblName
WHERE varValue > 10
ORDER BY UniqueID
SELECT TOP 10
varValue
FROM #tblName
WHERE varValue > 20
ORDER BY UniqueID
...etc.
Alternatively, you could delete those 10 rows from the table as you loop through your process. Then you could just use the same query each time:
SELECT TOP 10
varValue
FROM #tblName
ORDER BY UniqueID
DELETE #tblName
WHERE varValue IN (
SELECT TOP 10
varValue
FROM #tblName
ORDER BY UniqueID
)
...loop...
November 29, 2006 at 9:46 am
Oops... that delete query should have been this:
DELETE #tblName
WHERE UniqueID IN (
SELECT TOP 10
UniqueID
FROM #tblName
ORDER BY UniqueID
)
November 29, 2006 at 2:54 pm
If you are using SQL 2005 you could try, I'm not sure if SQL 2000 will allow the use of a variable in the top clause.
Declare
@Top INT
Set
@Top = 10
Select
Top (@Top) Item_id From Items Where NOT Item_id IN((Select Top (@Top) Item_id From items))
November 29, 2006 at 2:59 pm
No it won't work in 2000.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply