12/11/2012

[SQL] Specify ORDER BY values in query for user defined sorting

In SQL, you can use the ORDER BY clause to sort the data in ascending or descending order; you would usually:

SELECTcolumn1, column2
FROM table t
ORDER BY t.column1 ASC; --or DESC

But that's not all; if you need, you can specify a sorting rule directly inside the ORDER BY clause, in case you want a non- alphabetical or numerical sorting:

SELECT column1, column2
FROM table t
ORDER BY (
    CASE WHEN t.column1='value1' THEN 1
    ...
    END
);



You can use this particular construction multiple times inside the same ORDER BY clause, as you would for the classic ASC/DESC predicate, even mixing the two styles:

SELECT column1, column2
FROM table t
ORDER BY t.column2 ASC, 
(
    CASE WHEN t.column1='value1' THEN 1
    ...
    END
);

No comments:

Post a Comment

With great power comes great responsibility.

Da grandi poteri derivano grandi responsabilità.