I have a table ("comments") full of comments, the date they were created, and, if they were pushed to the front via a PHP button, the date of that also (otherwise the date will be '0000-00-00'). What I'd like is the table to be displayed as normal, but to have the comments that were "repushed" to be pushed to a place within the table with an ORDER BY of date created ("created").
Here's an example of what I'd like to achieve, which involes pushing an old comment (ID: 4) to the top (NOTE: there are no duplicates ID's in the table, only the query result will duplicate repushed records as shown here):
id | comment | created | repushed
---+---------+---------------------+-------------------
4 | hello | 2015-05-11 06:11:12 | 2015-11-22 11:17:01
23 | recent | 2015-05-22 12:18:23 | 0000-00-00 00:00:00
22 | recent | 2015-05-22 11:15:43 | 0000-00-00 00:00:00
21 | recent | 2015-05-22 10:23:10 | 0000-00-00 00:00:00
4 | hello | 2015-05-11 06:11:12 | 2015-11-22 11:17:01
So I'm trying to use UNION to combine all comments with repushed comments (so all repushed comments will appear duplicated once queried), but then I want to treat the duplicated repushed comments as if they were created at the time they were repushed, so I would need to swap the "created" and "repushed" column values of duplicates to get this effect, and then order by "created".
SELECT *
FROM comments
WHERE repushed != '0000-00-00 00:00:00'
UNION ALL
SELECT *
FROM comments
ORDER BY created DESC
Of course this doesn't work as intended since I don't know how to swap the two column values around.
Working with what you already have try this:
SELECT id,comment,repushed as created,repushed
FROM comments
WHERE repushed != '0000-00-00 00:00:00' group by id
UNION ALL
SELECT *
FROM comments group by id ORDER BY created DESC
This will rename the column repushed to created