CODEWITHSUNDEEP
sqlsql-serversortingstored-procedures
user2571510
user2571510

Reputation: 11377

SQL Server: create manual sort order

I am using the following stored procedure in order to fetch items from a table which works fine so far. Is there a way to change this so that if col3 equals "n/a" then these values should get added at the end of the list and everything else gets sorted as is ? I want to achieve that the "n/a" values do not appear in the middle of my list because they start with "n".

The same would also work if they get the "sortID" 99 assigned as all other items have a lower, numeric "sortID".

My SP (working): 

ALTER PROCEDURE [dbo].[FetchItems]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT      col1,
            col2,
            col3,
            col4,
            col5,
            sortID
    FROM        FetchItems
    WHERE       itemStatus = 'active'
    ORDER BY    sortID, col3
    FOR XML PATH('itemList'), ELEMENTS, TYPE, ROOT('ranks')
END

Thanks for any help with this, Tim.

Upvotes: 2

Views: 2190

Answers (2)

M.Ali
M.Ali

Reputation: 69494

ORDER BY 
    CASE WHEN col3 <> 'N/A' THEN Col3 ELSE 'ZZZZZ' END,
         sortID

Upvotes: 1

suff trek
suff trek

Reputation: 39767

Sure, you can do:

 ORDER BY CASE col3 WHEN 'n/a' THEN 1 ELSE 0 END,  sortID, col3

The CASE expression will produce 1 for all "n/a" values and 0 for the rest, so sorting by this expression first will effectively place all 'n/a' values last.

Upvotes: 3

Related Questions