July 22, 2008 at 4:42 am
Dear All
I have a stored procedure that is outputting a PageID and StatusID. PageIDs can be any integer and also 0, while StatusID can be 1,4 or null.
I wish to output something first in the StatusID order, and then in the PageID order. So I wish to output something like
PageIDStatusID
11
21
41
61
71
91
24
44
54
64
84
94
01
01
04
04
04
WITH ResourcesRN AS
(
SELECT ROW_NUMBER() OVER (ORDER BY pr.PageID DESC, er.StatusId ASC) AS RowNum,
COALESCE(pr.PageID, 0) AS PageID, er.StatusID
FROM dbo.Resources er
LEFT JOIN(
SELECTfk_resourceId,
MAX(fk_PageID) AS PageID
FROMPageResources
GROUP BYfk_resourceId
) AS pr ON pr.fk_resourceId = er.resourceId
)
SELECT * FROM ResourcesRN
WHERE RowNum BETWEEN (1 - 1) * 1000 + 1
AND 1 * 1000
Thanks for your help and time
Johann
July 22, 2008 at 4:52 am
Like this?
...order by case when PageId > 0 then 1 end desc, StatusID, PageID
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 22, 2008 at 4:59 am
ok I got to this
ORDER BYCASE PageID
WHEN 0 THEN 0
ELSE 1
END,
StatusId,
PageID
However the PageID 0 is displaying first before the other PageID's. I want it to display last
July 22, 2008 at 5:01 am
ok got it!
ORDER BYCASE PageID
WHEN 0 THEN 1
ELSE 0
END,
StatusID,
PageID
Thanks guys
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply