MySQL ordering a date range to the beginning, then ordering everything else by other criteria
I have a table of items that have a "Da开发者_高级运维te 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?
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.
精彩评论