I have a table:

and I have a data set : [{id:2:weight:8},{id:5,weight:6},{id:4,weight:3}]

I want to get the products which price < 5 and sort them first by weight asc from the dataset above and then sort them by price desc, so the result should be:

[{
id:4,product_id:4,id:4
},{
id:2,product_id:2,id:2
},{
id:3,product_id:3,id:3
},{
id:1:product_id:1,id:1
}]

CodePudding user response：

You can use CASE syntax to get the desired results.

SELECT * FROM (
    SELECT t.id, t.product_id, t.price, 
    CASE 
        WHEN t.id=2 THEN 8
        WHEN t.id=5 THEN 6
        WHEN t.id=4 THEN 3
        ELSE 0
    END AS weight
    FROM mytable t
    WHERE t.price < 5
) res ORDER BY res.weight ASC, res.price DESC;

CodePudding user response：

you can specify the way to short for each column declared in the sorted list. here is an example:

select *
from myDataset
where price < 5
order by price desc, weight asc