SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    User Declared Sort Order

    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?
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  2. #2
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    ORDER BY SortKey
    Dan

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by epaisley
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Non-Member DaveMichaels's Avatar
    Join Date
    Nov 2004
    Location
    US
    Posts
    535
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DaveMichaels
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    This also works...

    Code:
    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
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that's too complex

    replace your 3 PATINDEXes with this one --

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •