Paging query contest

OK, technically a contest has to have a prize, so... you'll be 'King of the Site' for a day. That's the prize. <big smile>

I came across a requirement to provide item numbers in files of 200. So, if there are 1100 items, there would be 5 files with 200, and the last would have 100.

The general idea behind this is 'paging'

I've done this before with different levels of success, this is the first that I've coded in SQL.

The query below provides a 'Start Item' and an 'End Item' in groups of 200 for the IV00101.

Can anyone see a more elegant way to do it?

Remember: King of the Site!

Related Articles

... and you 'll find more on the SQL (General) Menu

IF EXISTS (SELECT name
    FROM   sysobjects
    WHERE  name = N'dd_IV00101_SEL_forExport2'
        AND type = 'P')
    DROP PROCEDURE dd_IV00101_SEL_forExport2
GO
 
CREATE PROCEDURE dd_IV00101_SEL_forExport2
 
AS
 
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
--get the row count, so we can get the last row
declare @rowcount int
select @rowcount = sum(1)
    from iv00101
 
 
select  ITEMNMBR,
        RowID
    into #tmp
    from (
        --this query returns all the ITEMNMBRS and an artificial RowID
        --we have to use a subquery because we can't put ROW_NUMBER in the WHERE clause
        SELECT
                ITEMNMBR,
                ROW_NUMBER() OVER (ORDER BY ITEMNMBR) AS ROWID
            FROM IV00101 i
    ) i
    --Use the MOD function (%) to get every 200th row, and the first and last row
    where convert(numeric(19,1),rowid) % 200 =  0 or Rowid = 1 or ROWID = @rowcount
    order by rowid
 
select
        --use the LAG function to get the previous ITEMNMBR
        isnull(lag(itemnmbr) over (order by RowID),'') as StartItem,
        ITEMNMBR as EndItem,
        RowID
    from #tmp
    --we don't want the first row, because there is no previous row
    where not rowid = 1
 
GO
grant exec on dd_IV00101_SEL_forExport2 to public
--sp_sps

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences