CODEWITHSUNDEEP
mysqlsql
user782104
user782104

Reputation: 13545

How to ordering the create_date and update_date in mysql

In my database, there is a table like this

student
=======
name
create_date timestamp current_timestamp
update_date timestamp null

The update date is default null, only when there is update the update_date will be set the timestamp.

The problem is , I would like to order the record by the date, if there is update_date, I would like to use the update_date, if update_date is null , then use the create_date

How to change the query from simply

Select name from student order_by create_date desc

to fulfill my case?

Thanks a lot

Upvotes: 0

Views: 727

Answers (1)

spencer7593
spencer7593

Reputation: 108400

You can use an expression to return the specified value

An ANSI-standard expression would be 

CASE WHEN update_date IS NOT NULL THEN update_date ELSE create_date END

In MySQL, we have several functions that can return an equivalent result: 

IF(update_date IS NOT NULL, update_date, create_date)

or

IFNULL(update_date,create_date)

or

COALESCE(update_date,create_date)

Use an expression like one of those in the ORDER BY clause

ORDER BY IFNULL(update_date,create_date) DESC

Upvotes: 1

Related Questions