2 ORDER BY's in 1 Stored Proc - 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)

2 ORDER BY's in 1 Stored Proc

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-22 : 06:40:38

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

PageID StatusID
1 1
2 1
4 1
6 1
7 1
9 1
2 4
4 4
5 4
6 4
8 4
9 4
0 1
0 1
0 4
0 4
0 4

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 (
SELECT fk_resourceId,
MAX(fk_PageID) AS PageID
FROM PageResources
GROUP BY fk_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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 06:45:17

[code]SELECT PageID,
StatusID
FROM Table1
ORDER BY CASE PageID
WHEN 0 THEN 0
ELSE 1
END,
StatusID,
PageID[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 06:51:05

quote:
Originally posted by Peso

SELECT PageID,
StatusID
FROM Table1
ORDER BY CASE PageID
WHEN 0 THEN 0
ELSE 1
END,
StatusID,
PageID

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

shouldnt there be a DESC for first filed in order by?

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-22 : 06:56:33

Hi Peso

Thanks

That partially worked, however I am getting the PageID 0 first before the other PageIDS. Is it possible to get the PageID 0 last?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 06:59:40

Good point.
Or change the constants?

DECLARE	@Sample TABLE (PageID INT, StatusID INT)

INSERT	@Sample
SELECT	2, 4 UNION ALL
SELECT	1, 1 UNION ALL
SELECT	0, 4 UNION ALL
SELECT	2, 1 UNION ALL
SELECT	5, 4 UNION ALL
SELECT	4, 1 UNION ALL
SELECT	6, 4 UNION ALL
SELECT	9, 1 UNION ALL
SELECT	4, 4 UNION ALL
SELECT	0, 1 UNION ALL
SELECT	8, 4 UNION ALL
SELECT	0, 4 UNION ALL
SELECT	6, 1 UNION ALL
SELECT	0, 4 UNION ALL
SELECT	7, 1 UNION ALL
SELECT	0, 1 UNION ALL
SELECT	9, 4

SELECT		PageID,
		StatusID
FROM		@Sample
ORDER BY	CASE PageID
			WHEN 0 THEN 1
			ELSE 0
		END,
		StatusID,
		PageID

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 07:00:07

quote:
Originally posted by monfu

Hi Peso

Thanks

That partially worked, however I am getting the PageID 0 first before the other PageIDS. Is it possible to get the PageID 0 last?

SELECT		PageID,
		StatusID
FROM		Table1
ORDER BY	CASE PageID
			WHEN 0 THEN 0
			ELSE 1
		END DESC,
		StatusID,
		PageID

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-22 : 07:01:18

COOL

that is what I was after

Can you explain to me what this means

ORDER BY CASE PageID
WHEN 0 THEN 1
ELSE 0
END,
StatusID,
PageID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 07:15:07

All records with PageID = 0 should be sorted last (group 1) and all other PageID should be sorted first (group 1).
Within the groups, records are sorted by PageID and StatusID.

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

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-22 : 07:23:34

Thanks Peso

Regards

Johann

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources