CODEWITHSUNDEEP
mysqlsql-order-by
Enkay
Enkay

Reputation: 1915

mysql order by field( with a twist)

I have a product table with a product name, two product attributes fields, and a price field.

The problem is that I can't change the way the database is structured. Each attribute fields are equivalent and either can be used.

basically :

 NAME      | ATTRIBUTE_1 | ATTRIBUTE_2 | PRICE
 Tshirt    | red         | small       | 25
 Tshirt    | medium      | red         | 20
 Tshirt    | blue        | medium      | 30
 Tshirt    | blue        | large       | 16

Not the best set up but that's how it is...

I want to do a query that will do the following :

Display all the blue tshirts first and order them by price Display all the other tshirts ordered by price after

I was thinking something like order by field, but since the color can be either in attribute 1 or 2 those fields need to be equivalents.

Any suggestions?

Upvotes: 3

Views: 539

Answers (2)

Daniel Vandersluis
Daniel Vandersluis

Reputation: 94253

SELECT NAME, ATTRIBUTE_1, ATTRIBUTE_2, PRICE
FROM products
ORDER BY (ATTRIBUTE_1 = 'blue' OR ATTRIBUTE_2 = 'blue') DESC, PRICE

(ATTRIBUTE_1 = 'blue' OR ATTRIBUTE_2 = 'blue') will be 1 if true, 0 if false, so sorting on that in descending order will sort the rows that have one attribute value of blue first.

Upvotes: 3

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171491

select name, attribute_1, attribute_2, price
from Product
order by 
    case 
        when ATTRIBUTE_1 = 'blue' or ATTRIBUTE_2 = 'blue' then 0 
        else 1 
    end, 
    price

Upvotes: 2

Related Questions