CODEWITHSUNDEEP
mysql
user1012181
user1012181

Reputation: 8726

Get SQL result ordered by a column value

I'm trying to get the results ordered by a column value. I have this basic query: 

SELECT `product_id`,
       (SELECT `name`
        FROM   `specifications` s
        WHERE  `specification_id` = sp.`specification_id`) AS Specification,
       `value`
FROM   `specs-product` sp
WHERE  `product_id` = '4'
       AND ( `specification_id` = '88'
              OR `specification_id` = '103'
              OR `specification_id` = '18'
              OR `specification_id` = '15'
              OR `specification_id` = '157'
              OR `specification_id` = '89'
              OR `specification_id` = '9'
              OR `specification_id` = '223'
              OR `specification_id` = '224'
              OR `specification_id` = '29'
              OR `specification_id` = '87'
              OR `specification_id` = '219'
              OR `specification_id` = '218'
              OR `specification_id` = '220' )

I've a column name in the table specifications called priority. I need to get the result ordered by this priority values.

I've tried to add order by s.'priority' But didn't work. How can I do this?

Table Structure as Follows:

specifications

+------------------+------+----------+
| specification_id | name | priority |
+------------------+------+----------+
| 1                | abc  |        5 |
| 2                | xxxx |        2 |
| 3 ababab         | 3    |          |
+------------------+------+----------+

PRODUCTS TABLE

+------------+------------+--+
| product_id | reference  |  |
+------------+------------+--+
|          1 | abc        |  |
|          2 | xxxx       |  |
|          3 | ababab     |  |
+------------+------------+--+

Upvotes: 0

Views: 39

Answers (1)

Philip Devine
Philip Devine

Reputation: 1169

Add

order by sp.'priority'

Assuming it is in the table. If this doesn't work we need your schema.

Edit

SELECT `product_id`,
s.name AS Specification,
`value` 
FROM `specs-product` sp 
inner join `specifications` s on s.`specification_id` = sp.`specification_id`
WHERE `product_id`='4' 
AND
(s.`specification_id`='88' or 
s.`specification_id`='103' or 
s.`specification_id`='18' or 
s.`specification_id`='15' or 
s.`specification_id`='157' or 
s.`specification_id`='89' or 
s.`specification_id`='9' or 
s.`specification_id`='223' or 
s.`specification_id`='224' or 
s.`specification_id`='29' or 
s.`specification_id`='87' or 
s.`specification_id`='219' or 
s.`specification_id`='218' or 
s.`specification_id`='220')
order by s.priority

Upvotes: 1

Related Questions