retrieve data by order

[AmrMohammed]

Greetings,

I'm having a table that I need to retrieve its data in a specific order based on a column value(s). I need to retrieve first all rows that have the value 0 in that column in ascending order (default order) then rows that have value(s) greater than 0 in a descending order.

[Reverend Jim]

If all values in that column have the same value (zero) then sorting on that column won't do much. Here is an example of sorting on two columns using the PUBS sample database

SELECT * FROM authors
 ORDER BY au_lname ASC, au_fname DESC

It sorts first by author last name (ascending), then by first name (descending).

[Bob Hensley]

Greetings,

I'm having a table that I need to retrieve its data in a specific order based on a column value(s). I need to retrieve first all rows that have the value 0 in that column in ascending order (default order) then rows that have value(s) greater than 0 in a descending order.

This sounds like two queries to me:

SELECT cols FROM table WHERE col = 0 ORDER BY col ASC
and
SELECT cols FROM table WHERE col > 0 ORDER BY col DESC

It's the pairing of the conditional and return order that makes me think this has to be done in two queries. But you can union the two:

SELECT cols FROM table WHERE col = 0 ORDER BY col ASC
UNION ALL
SELECT cols FROM table WHERE col > 0 ORDER BY col DESC

You can eliminate duplicate results by dropping the ALL statement from UNION.

[Jefferson_1]

You can achieve it by doing the following
select cols from table order by case when col = 0 then 0 else 1 end, col desc

The first order by was to determine the zeroes and place it on the top list.
The second order was to achieve the descending order you want.