CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-by
JohnnyRocket
JohnnyRocket

Reputation: 55

SQL Query - order by date but also group

OK Ladies and Gents, I would really appreciate some help:

This is my table:

ID     postID    replyID    content    entry_date  
1       0         40          hey         12/7  
2       0         40          hi          12/8  
3       0         40          whatsup     12/9  
4       2         40          why?        12/10  
5       0         40          who?        12/11

I need to run a query to get it like this:

ID     postID    replyID    content    entry_date  
1       0         40          hey         12/7  
2       0         40          hi          12/8  
4       2         40          why?        12/10  
3       0         40          whatsup     12/9  
5       0         40          who?        12/11

You will see that ID 3 and 4 have switched. So basically I need to ASC by entry_date, unless ID = POSTID, then I need those two grouped together and also ASC by entry_date for those two.

Here is what I have tried but I am totally lost:

SELECT t1.ID, t1.postID, t1.replyID, t1.content, t1.entry_date
  FROM discussion t1, discussion t2
 WHERE t1.replyID = '40' AND t1.ID = t2.postID
 ORDER BY t1.entry_date ASC

Which basically does nothing but finds where a row of ID = a row of postID

Upvotes: 1

Views: 815

Answers (1)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You can add a CASE statement to your ORDER BY clause 

ORDER BY 
  CASE WHEN postID = 0 THEN ID ELSE postID END
  , entry_date

doing so, you can discard the join alltogether and the entire statement could be simplified to

SELECT  ID
        , postID
        , replyID
        , content
        , entry_date
FROM    discussion
ORDER BY 
       CASE WHEN postID = 0 THEN ID ELSE postID END
       , entry_date

Note that I do assume the entry date being a DATETIME column, not a VARCHAR column.

Upvotes: 5

Related Questions