User Declared Sort Order

[epaisley]

I'm using ms SQL Server and I was wondering if there is way to to order your query in a user specified way. For example, I have a table that looks like this.

Table: Names

Userid Name
1 Bob
2 Jen
3 Eric
4 Jeff
5 Tammy
6 Dave


And I want it to come out in this specific order of user id's: 3,1,6,5,2,4

Is this even possible?

[danfran]

e,

The answer to your question is this:

Add a field to your table and name it "SortKey" or "DisplayOrder". Then, in this column, put them in order as you see fit. Maybe even go in steps of 10 so you can insert records later to fit people in-between.

Don't forget to add this to the end of your queries:

ORDER BY SortKey

Dan

[r937]

And I want it to come out in this specific order of user id's: 3,1,6,5,2,4

yes, this is possible

order
    by case when Userid = 3 then 1 else 0 end desc
     , case when Userid = 1 then 1 else 0 end desc
     , case when Userid = 6 then 1 else 0 end desc
     , case when Userid = 5 then 1 else 0 end desc
     , case when Userid = 2 then 1 else 0 end desc 
     , case when Userid = 4 then 1 else 0 end desc

[DaveMichaels]

That won't scale

[r937]

That won't scale

[epaisley]

SELECT CustID, SiteID, UserID,
    PATINDEX('%,'+CAST(UserID AS varchar)+',%','2,1,6,5,3,4') +
    PATINDEX(	 CAST(UserID AS varchar)+',%','2,1,6,5,3,4') +
    PATINDEX('%,'+CAST(UserID AS varchar)	 ,'2,1,6,5,3,4')
  AS OrderBy
  FROM custuser
  WHERE custid = 67
    and siteid = 1
    and userid in (2,1,6,5,3,4)
  ORDER BY OrderBy

[r937]

that's too complex

replace your 3 PATINDEXes with this one --

PATINDEX('%,'+CAST(fld AS varchar)+',%', ','+'2,1,6,5,3,4'+',')