Results 1 to 5 of 5

Thread: Order By

  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: Order By

    Hi

    I'm trying to figure out how to order data so rows which begin in a certain sequence are returned first, followed by the rest of the data in ascending order.

    id name
    1 name1
    2 name2
    3 xy.name3
    4 name4
    5 xy.name5
    6 xy.name6
    7 name7

    how would i structure the ORDER BY clause so it came out like:

    xy.name3
    xy.name5
    xy.name6
    name1
    name2
    name4
    name7

    I'm sure if its possible you would use the wild card some how just not sure of the correct syntax!

    cheers

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    select name
    from table
    order by case when name like 'xy.%' then 1 else 2 end, name

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Thanks for that. Would like to look into the numbering part further (...1 else 2...) - is there a phrase associated with it so i can look it up?

    cheers

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mattock
    is there a phrase associated with it so i can look it up?
    it's a simple logical construction

    the CASE expression tests for something on each row, and if it's true, it assigns the value 1 to a pseudo-column for that row, otherwise it assigns 2

    the solution works because 1 comes ahead of 2 if you sort on those values

    for additional examples, search on the phrase "custom sort order"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    cheers rudy

Posting Permissions

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