Paging with a condition - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Paging with a condition

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2008-08-11 : 10:46:52

Hi All,

I need to do paging, with the following condition.
The number of records in each page, should be
a. 5 (max)
b. same data (in a particular column) containing records should not go to the next page

There can be 1~3 records having same data.

Here is an example (the column, of which data should not go to next page is Col1):
Records would look like


Col1  Col2 Col3
aaa   pre  123
aaa   hgg  456
aaa   sre  223
bbb   dhd  563
bbb   dhr  349
bbb   ehr  385
ccc   wew  766
ddd   wyw  761
ddd   rei  436

I need the data as follows in 3 pages

Page1


Col1  Col2 Col3
aaa   pre  123
aaa   hgg  456
aaa   sre  223

Page2


Col1  Col2 Col3
bbb   dhd  563
bbb   dhr  349
bbb   ehr  385
ccc   wew  766

Page3


Col1  Col2 Col3
ddd   wyw  761
ddd   rei  436

Thanks

Srinika

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 11:27:23

[code]DECLARE @Sample TABLE (Col1 VARCHAR(3), Col2 VARCHAR(3), Col3 INT)

INSERT @Sample
SELECT 'aaa', 'pre', 123 UNION ALL
SELECT 'aaa', 'hgg', 456 UNION ALL
SELECT 'aaa', 'sre', 223 UNION ALL
SELECT 'bbb', 'dhd', 563 UNION ALL
SELECT 'bbb', 'dhr', 349 UNION ALL
SELECT 'bbb', 'ehr', 385 UNION ALL
SELECT 'ccc', 'wew', 766 UNION ALL
SELECT 'ddd', 'wyw', 761 UNION ALL
SELECT 'ddd', 'rei', 436

;WITH Yak (Col1, Items, SeqID)
AS (
SELECT Col1,
COUNT(*),
ROW_NUMBER() OVER (ORDER BY Col1)
FROM @Sample
GROUP BY Col1
), cte (Col1, Items, PageItems, SeqID, PageNum)
AS (
SELECT Col1,
Items,
Items,
SeqID,
1
FROM Yak
WHERE SeqID = 1

UNION ALL

SELECT y.Col1,
y.Items,
CASE
WHEN c.PageItems + y.Items <= 5 THEN c.Items + y.Items
ELSE y.Items
END,
y.SeqID,
CASE
WHEN c.PageItems + y.Items <= 5 THEN c.PageNum
ELSE c.PageNum + 1
END
FROM cte AS c
INNER JOIN Yak AS y ON y.SeqID = c.SeqID + 1
)

SELECT s.col1,
s.col2,
s.col3,
c.PageNum
FROM @Sample as s
inner join cte AS c ON c.Col1 = s.Col1[/code]

E 12°55'05.25"
N 56°04'39.16"

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2008-08-13 : 12:11:15

Thanks Peso,
It worked.

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources