2 ORDER BY's in 1 Stored Proc

  • 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

  • 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.

  • 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

  • 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