CODEWITHSUNDEEP
mysqlsql-order-by
Andrew Brown
Andrew Brown

Reputation: 125

Tricky MYSQL ORDER BY clause

I have a table with first name, last name, and follow-up column. Im currently displaying the results in the following order

ORDER BY followup IS NULL, followup asc, lastname

This places rows that have follow-ups at the top with the follow-up date closest to today first. Then if follow-up date is NULL it sorts by last name.

What I actually want is to display follow-ups that are within a week of todays date first then NULL followups by last name, followed by the rest of the follow-ups that are over a week from their due date.

Not sure if this is feasible with MYSQL and may require a custom sort function in PHP.

Any help would be appreciated.

Upvotes: 0

Views: 27

Answers (1)

RockBoro
RockBoro

Reputation: 2473

use CASE

order by case when followup is null then concat('2',lastname)
              when date_add(followup, interval 7 day) > current_date 
              then concat('1',lastName)
              else concat('3',lastName) end

Upvotes: 1

Related Questions