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.

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 …

## All 3 Replies

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).

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.

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.