Get consecutive rows.

I don't think this is about paging.

Access, care to explain what you consider "consecutive"?

--
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Actually I think it's quite more tricky than just a TOP n. Will this work?<br /><pre id="code"><font face="courier" size="2" id="code"><br />USE tempDB<br />IF OBJECT_ID('access')&gt;0<br /> DROP TABLE access<br />CREATE TABLE access<br />(<br /> Invoice_Number INT<br /> , invoice_date SMALLDATETIME<br /> , invoice_status VARCHAR(10)<br />)<br />INSERT INTO access SELECT 1 , '01/12/2001 12:00 AM' , 'Active'<br />UNION ALL SELECT 2 , '03/03/2001 2:22 PM' , 'Closed'<br />UNION ALL SELECT 4 , '05/12/2001 11:00 AM' , 'Closed'<br />UNION ALL SELECT 5 , '06/11/2001 10:00 AM' , 'Active'<br />UNION ALL SELECT 6 , '11/12/2003 19:00 PM' , 'Closed'<br />UNION ALL SELECT 9 , '12/12/2003 12:00 PM' , 'Sent'<br />UNION ALL SELECT 10 , '01/27/2004 9:00 AM' , 'Sent'<br /><br />DECLARE @MyValue INT<br />SET @MyValue = 3<br /><br />SELECT t1.*<br /> FROM access t1<br /> JOIN <br /> (<br /> SELECT MAX(MyStart) MyStart , My_End <br /> FROM <br /> (SELECT t.Invoice_Number MyStart <br /> FROM access t <br /> WHERE NOT EXISTS<br /> (SELECT * <br /> FROM access <br /> WHERE Invoice_Number = t.Invoice_Number-1))starts <br /> JOIN <br /> (SELECT t.Invoice_Number My_End <br /> FROM access t <br /> WHERE NOT EXISTS<br /> (SELECT * <br /> FROM access <br /> WHERE Invoice_Number = t.Invoice_Number+1) )ends <br /> ON MyStart &lt;= My_End <br /> GROUP BY My_End <br /> HAVING My_End + 1 - MAX(MyStart) = @MyValue) x<br /> ON t1.Invoice_Number BETWEEN x.MyStart AND x.My_End<br /><br /> <br /><br />Invoice_Number invoice_date invoice_status <br />-------------- ------------------------------------------------------ -------------- <br />4 2001-05-12 11:00:00 Closed<br />5 2001-06-11 10:00:00 Active<br />6 2003-11-12 19:00:00 Closed<br /><br />(3 row(s) affected)<br /></font id="code"></pre id="code"><br />I guess it can be simplified, but honestly it's late in my place and I'm tired. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

I suspected that this would cause a problem. For that case, you should modify the above query to:


SELECT t1.*
FROM access t1
JOIN
(
SELECT TOP 1 MAX(MyStart) MyStart , My_End
FROM
(SELECT t.Invoice_Number MyStart
FROM access t
WHERE NOT EXISTS
(SELECT *
FROM access
WHERE Invoice_Number = t.Invoice_Number-1))starts
JOIN
(SELECT t.Invoice_Number My_End
FROM access t
WHERE NOT EXISTS
(SELECT *
FROM access
WHERE Invoice_Number = t.Invoice_Number+1) )ends
ON MyStart <= My_End
GROUP BY My_End
HAVING My_End + 1 - MAX(MyStart) = @MyValue
ORDER BY MyStart) x
ON t1.Invoice_Number BETWEEN x.MyStart AND x.My_End

However, reviewing the query at all, I think the following might be more effective. At least it seems to produce a leaner execution plan:


SELECT t.*
FROM access t
JOIN
(SELECT TOP 1 MIN(Invoice_Number) AS My_Start, MAX(Invoice_Number) AS My_End
FROM
(
SELECT Invoice_Number, Invoice_Number -
(SELECT COUNT(Invoice_Number) - MIN(Invoice_Number)
FROM access t2
WHERE t1.Invoice_Number >= t2.Invoice_Number) AS Run_Offset
FROM access t1
) P
GROUP BY Run_Offset
HAVING COUNT(Invoice_Number) > @MyValue - 1
ORDER BY My_Start) x
ON t.Invoice_Number BETWEEN x.My_Start AND x.My_End



--
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Addition:
In case you have the requirement that the first run having equal or more consecutive Invoice_Numbers than the number entered by the user, you should modify the HAVING clause to:


HAVING COUNT(Invoice_Number) >= @MyValue

And in case the consecutive Invoice_Numbers MUST exactly match the entered value (thus probably ignore lesser Invoice_Numbers with more consecutive numbers), change the HAVING clause to:


HAVING COUNT(Invoice_Number) = @MyValue



--
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Well, if the first performs better, you should take this. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Btw, when you're on SQL Server 2005, you might be able to take advantage of these new OLAP T-SQL functions. Unfortunately I can't get my hands on that version right now, so I can't help you here. <br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>