CODEWITHSUNDEEP
sql-serversql-order-by
Jesper
Jesper

Reputation: 999

SQL Server order by different values

I have a table where status can be either GOLD, SILVER, ACTIVE or INACTIVE.

I would like to sort by that... in that order and take top X. How can I write that in my query?

Currently I am filling up a list by first querying for GOLD and then checking the size of the list, then querying for SILVER and again checking the size etc.

Upvotes: 1

Views: 5183

Answers (3)

Ihtesham
Ihtesham

Reputation: 1

This may help someone. Say you have a table(MyTable) with a column(StatusTypeID) as a foreign key from 'StatusTypes' table. You can implement a procedure to do dynamic sorting like this.

CREATE PROCEDURE DynamicSort(@SortOrder int)   
AS
BEGIN

SELECT * FROM MyTable
ORDER BY
CASE WHEN StatusTypeID = @SortOrder then 1
END 

DESC

END
--  exec  DynamicSort 7

Upvotes: -1

Vinny Roe
Vinny Roe

Reputation: 901

If you were being good, you should really normalize this out to a separate table:

statusvalues
id   displaytext sortorder
1    GOLD        10
2    SILVER      20
3    ACTIVE      30
4    INACTIVE    40

then store id as your status value on main table. Join from your main table to this one, to get the displaytext and sortorder, and sort appropriately.

NB 1 I leave gaps in the sort order, so that if you have to insert another, you can put it at e.g. 15 without having to change any other values. NB 2 This means that, if your customer decides that "GOLD" should now display as "PREMIUM", you change 1 data item value, and your code is unchanged. Might save you a lot of time...! But this may be overkill - up to you to assess.

Upvotes: 5

Mitch Wheat
Mitch Wheat

Reputation: 300817

SELECT TOP X *
FROM myTable
ORDER BY
  CASE
       WHEN Status = 'GOLD' THEN 4
       WHEN Status = 'SILVER' THEN 3
       WHEN Status = 'ACTIVE'  THEN 2
       WHEN Status = 'INACTIVE' THEN 1
  END DESC

Upvotes: 14

Related Questions