CODEWITHSUNDEEP
mysqlsqlsortingsql-order-by
JayKandari
JayKandari

Reputation: 1248

How can ORDER BY clause sort the records starting from 1 instead of 0

In a typical ORDER BY clasue, if a column having only numerical values. then, how is it possible to make a query so that the result would sort the numbers from 1 to infinity and rest zeroes and negative numbers come at last.

I have this table : TABLEONE

col1 | col2
0    | Zero
5    | Five
-2   | NA
1    | One
2    | Two
7    | NA

if I pass this query:

SELECT * FROM TABLEONE ORDER BY col1;

The rows are sorting in this order

-2, 0, 1, 2, 5, 7

What I want is : 

1, 2, 5, 7, 0, -2, -4 ... etc..

Is it possible to alter our existing query to get that result. Thanks.

Upvotes: 1

Views: 223

Answers (2)

Ike Walker
Ike Walker

Reputation: 65577

SELECT * 
FROM TABLEONE 
ORDER BY (col1 > 0) DESC, ABS(col1) ASC;

Upvotes: 0

GarethD
GarethD

Reputation: 69789

You could use the following:

SELECT  *
FROM    T
ORDER BY SIGN(Col1) DESC, ABS(Col1);

Example on SQL Fiddle

Upvotes: 4

Related Questions