select top 10 and more.

  • 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

  • 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?

  • 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

  • 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

  • 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

  • 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...

  • Oops... that delete query should have been this:

    DELETE #tblName

    WHERE UniqueID IN (

     SELECT TOP 10

     UniqueID

     FROM #tblName

     ORDER BY UniqueID

    )

  • 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))

     

  • 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