CODEWITHSUNDEEP
mysqlsql
Crinsane
Crinsane

Reputation: 818

How to order this query

I've got a query that gets products out of a mysql db. It's something like this:

SELECT p.name, p.description, p.price, ps.sizename 
FROM products p
JOIN product_sizes ps ON ps.id = p.size_id

The sizename this query it fetching, could be S, M, L etc, but also 38, 39, 40 etc.

What I would like now, is for this query, is to order by sizename, but order it so that when the size is numeric, it orders numeric but when it's something like S, M, L, it sorts in the right way, so S, M, L, XL, XXL, and not alphabetical L, M, S, XL, XXL.

How to do this?

Upvotes: 3

Views: 192

Answers (5)

Joe G Joseph
Joe G Joseph

Reputation: 24046

try 

   ORDER BY 
        CASE    WHEN sizename='S'    THEN 38 
                WHEN sizename='M'    THEN 39
                WHEN sizename='L'    THEN 40
                WHEN sizename='XL'   THEN 42
                WHEN sizename='XXL'  THEN 44
                WHEN sizename REGEXP ('[0-9]') THEN sizename
        END

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460058

This should work:

SELECT p.name, p.description, p.price, ps.sizename 
FROM products p
JOIN product_sizes ps ON ps.id = p.size_id
order by case when CONVERT(sizename, SIGNED INTEGER) IS NOT NULL 
    THEN CONVERT(sizename, SIGNED INTEGER) 
    ELSE -CAST(-1 AS UNSIGNED) END ASC
, case sizename WHEN 'S' THEN 1 
            WHEN 'M' THEN 2
            WHEN 'L' THEN 3
            WHEN 'XL' THEN 4
            WHEN 'XXL' THEN 5 END ASC
, sizename ASC

Here's a fiddle: http://sqlfiddle.com/#!2/4a3fb/1/0

Upvotes: 2

Karazyabko
Karazyabko

Reputation: 75

Try to specify existing values in order you want, something like that:

SELECT p.name, p.description, p.price, ps.sizename 
FROM products p
JOIN product_sizes ps ON ps.id = p.size_id
ORDER BY FIELD(ps.sizename,'S','M','L','XL','XXL',...)

Upvotes: 0

juergen d
juergen d

Reputation: 204746

order by case when ps.sizename in (38,39,40)
              then ps.sizename
              when ps.sizename = 'XS'
              then 1
              when ps.sizename = 'S'
              then 2
              when ps.sizename = 'M'
              then 3
              when ps.sizename = 'L'
              then 4
              when ps.sizename = 'XL'
              then 5
              when ps.sizename = 'XXL'
              then 6
         end

You can adjust the numbers for the alphanumeric order like you want

Upvotes: 1

podiluska
podiluska

Reputation: 51494

You could do it with a CASE statement, but I would recommend adding a SortOrder column to your Size table, and ordering by that.

Upvotes: 0

Related Questions