CODEWITHSUNDEEP
phpmysqldateselect
Jake
Jake

Reputation: 4234

MySQL ordering a date range to the beginning, then ordering everything else by other criteria

I have a table of items that have a "Date Created" field, and I'm trying to figure out a way to order them so that items created in the past two weeks appear first, and items that were not created in the last two weeks appear after that, sorted alphabetically.

I know how to do it with multiple sql queries, but I'd prefer not to do that if possible. Is it possible to do this?

Upvotes: 1

Views: 904

Answers (1)

Nicola Cossu
Nicola Cossu

Reputation: 56367

select * from table
order by
case when date_created > curdate() - interval 2 week then 1 else 2 end,item

UPDATED ANSWER

(select * from table
where date_created > curdate() - interval 2 week 
order by date_created desc limit 0,10000000000)
union all
(select * from table
where date_created < curdate() - interval 2 week 
order by item
limit 0,10000000000)

LIMIT's use is necessary when you have to apply both asc and desc sorting within union.

Upvotes: 3

Related Questions