January 10, 2010 at 11:26 pm
Hi all
i need write a pagging SQL with data same as above. Page break with condiction (order by val1,val2,val3):
- if 3 VAL2 is same: break page
- if VAL2 change: break page
- if VAL1 change: break page
I'm writting a SQL same as above but i don't like it. Any suggesstion? Please help me. Thanks
DECLARE @data TABLE
(
VAL1 int NOT NULL,
VAL2 int NOT NULL,
VAL3 int NOT NULL
);
INSERT @data
VALUES (1,1,1),
(1,1,2),
(1,1,3),
(1,2,1),
(1,2,2),
(1,2,3),
(1,2,4),
(1,2,5),
(1,2,6),
(1,2,7),
(1,2,8),
(2,1,1),
(2,1,2),
(2,1,3),
(2,1,4),
(2,1,5),
(2,1,6);
SELECT
MY.VAL1,MY.VAL2,MY.VAL3,TEMP6.REALPAGE
FROM
@data MY
LEFT JOIN
(
SELECT
TEMP5.*,ROW_NUMBER() OVER (ORDER BY VAL1,VAL2,VAL3) AS REALPAGE
FROM
(
SELECT
TEMP1.VAL1,TEMP1.VAL2,MAX(TEMP1.VAL3) AS VAL3,
CASE WHEN
TEMP1.ROWNUM_VAL12 % 3=0
OR TEMP1.VAL1<>TEMP2.VAL1
OR TEMP1.VAL2<>TEMP2.VAL2
OR TEMP2.VAL1 IS NULL
THEN 'BREAK' ELSE NULL END AS BREAKPAGE
FROM
(
SELECT
VAL1,VAL2,VAL3,
ROW_NUMBER() OVER (PARTITION BY VAL1,VAL2 ORDER BY VAL2) AS ROWNUM_VAL12,
ROW_NUMBER() OVER (ORDER BY VAL1,VAL2) AS ROWNUM_VAL2
FROM @data MY
) TEMP1
LEFT JOIN
(
SELECT
VAL1,VAL2,VAL3,
ROW_NUMBER() OVER (ORDER BY VAL1,VAL2) AS ROWNUM_VAL2
FROM
(
SELECT
*
FROM
@data MY
EXCEPT SELECT TOP 1 * FROM @data MY
) TEMP3
) TEMP2 ON TEMP2.ROWNUM_VAL2=TEMP1.ROWNUM_VAL2
GROUP BY TEMP1.VAL1,TEMP1.VAL2,TEMP1.ROWNUM_VAL12,TEMP2.VAL1,TEMP2.VAL2
) TEMP5
WHERE TEMP5.BREAKPAGE IS NOT NULL
) TEMP6 ON TEMP6.VAL1=MY.VAL1 AND TEMP6.VAL2=MY.VAL2 AND TEMP6.VAL3=MY.VAL3
January 11, 2010 at 8:40 am
This seems to work for me.
;WITH ROWSCTE(VAL1,VAL2,VAL3,RECORDNUMBER,ROWNUMBER,PAGENUMBER) AS
(SELECT *,ROW_NUMBER() OVER(ORDER BY VAL1,VAL2,VAL3) as recordnumber,ROW_NUMBER() OVER(PARTITION BY VAL1,VAL2 ORDER BY VAL1, VAL2, VAL3) AS RowNumber,
case when (ROW_NUMBER() OVER(PARTITION BY VAL1,VAL2 ORDER BY VAL1, VAL2, VAL3))%3=1 then 1 else 0 end as pageno FROM @data
)
SELECT val1,val2,val3,
rownumber,
pagenumber+COALESCE((SELECT SUM(pagenumber)
FROM ROWSCTE b
WHERE b.RECORDNUMBER < a.RECORDNUMBER),0)
AS PAGENUMBER
FROM ROWSCTE a
January 11, 2010 at 6:42 pm
Perfect. Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply