CODEWITHSUNDEEP
mysqlsql-order-by
Brett
Brett

Reputation: 20059

Changing the order by results depending on column value?

I am trying to sort a column by the lowest value, BUT if the value is 0 then I want all columns with that value placed at the end and NOT the start.

Can this be done?

Just say query is currently..

SELECT * FROM auctions ORDER BY a.discount_start_date ASC

Is there a method where I can do what I want?

Upvotes: 0

Views: 45

Answers (3)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

You can use this:

SELECT * FROM auctions 
ORDER BY
  CASE WHEN a.discount_start_date = 0 THEN 0 ELSE 1 END CASE DESC,
  a.discount_start_date ASC;

I suggests that you use appropriate data type for your data.

Upvotes: 1

Ravinder Reddy
Ravinder Reddy

Reputation: 24012

Try ORDER BY FIELD on column a.discount_start_date.

Example: 

SELECT * FROM auctions 
ORDER BY FIELD( a.discount_start_date, '0000-00-00'), a.discount_start_date;

All the records that have a zero date in discount_start_date column are placed last and rest are sorted in ascending order and placed on top.

Refer to:
MySQL: FIELD(str,str1,str2,str3,...)

Upvotes: 1

Simon
Simon

Reputation: 2960

Use a case statement in the order by, such that if the value is 0, set it to the maximum possible value for that field, else return the value of the field.

Upvotes: 1

Related Questions