CODEWITHSUNDEEP
mysqljoin
Joeeee
Joeeee

Reputation: 1266

Mysql self join with multiple order by

assume we have table

id, title, date

I need to build 1 query to:

select date = TODAY, order by id
select data < TODAY, order by date desc,
select data > TODAY, order by date asc,

Upvotes: 0

Views: 224

Answers (1)

sgeddes
sgeddes

Reputation: 62851

I think you need to use UNION and sub queries:

SELECT * FROM (
    SELECT *
    FROM YourTable
    WHERE Date(dateField) = Date(Now())
    ORDER BY ID 
) t1
UNION
SELECT * FROM (
    SELECT *
    FROM YourTable
    WHERE dateField < Now()
    ORDER BY dateField DESC
) t2
UNION
SELECT * FROM (
    SELECT *
    FROM YourTable
    WHERE Date(dateField) > Now()
    ORDER BY dateField 
) t3

Here is a simplified SQL Fiddle example.

Good luck.

Upvotes: 2

Related Questions