SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question SELECT ... ORDER BY issue

    Hi there,

    I am working on a watch-reseller's site. Each product has a grade 'Unworn', 'Excellent', 'Good', 'Average'.

    I need to order by Grade, but obviously MySQL will order alphabetically. So 'Unworn', 'Good', 'Excellent', 'Average', or vise versa. Not good. The obvious solution would be to have a `grades` table and then order the results by grade id, with an INNER JOIN... but before I change my database design, I was wondering if anyone could suggesta more elegant solution.

    Many thanks,
    Mike

  2. #2
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I like your solution.

    one alternative is to use the MySQL case and assign it a number as part of your SQL.
    e.g.
    select CASE grade when 'Unworn' then 1
    when 'Excellent' then 2
    when 'Good' then 3
    when 'Average' then 4
    end
    from Product

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    stacked CASE conditions will do it

    also, this --
    Code:
    ORDER BY FIELD(grade, 'Unworn', 'Excellent', 'Good', 'Average')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you use order by clause then it would sort it by alphabetical order, your solution which you specified of giving grade_id would work , other wise go for procedure.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by timu123 View Post
    If you use order by clause then it would sort it by alphabetical order...
    did you happen to notice the post immediately above yours?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hey r937 thanks for your post, I it the first time round, but yeah worked a treat.

    Thanks again.


Tags for this Thread

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
  •