6

I want to order one mysql table by two strtotime timestamps from two different columns. I've got the following mysql command:

SELECT * FROM (
(SELECT '1' AS `table`,  `vid_req_timestamp` AS `timestamp`, `title` FROM `movies` WHERE `vid_req` = '1')
UNION
(SELECT '2' AS `table`,  `ost_req_timestamp` AS `timestamp`, `title` FROM `movies` WHERE `ost_req` = '1')
)
ORDER BY `timestamp` DESC

This gives me an error:

#1248 - Every derived table must have its own alias

I want to combine vid_req_timestamp and ost_req_timestamp and make those descending. And it's important to know where the timestamp came from (somehow).

Paul Gerarts
  • 1,197
  • 1
  • 8
  • 10

1 Answers1

14

In this case, the derived table that requires an alias is the one that you are SELECTing * from.

Indentation helps make that clearer.

SELECT * FROM 
(
    (SELECT '1' AS `table`,  `vid_req_timestamp` AS `timestamp`, `title` FROM `movies` WHERE `vid_req` = '1')
    UNION
    (SELECT '2' AS `table`,  `ost_req_timestamp` AS `timestamp`, `title` FROM `movies` WHERE `ost_req` = '1')
) AS `some_table_name_lol_this_is_an_alias`
ORDER BY `timestamp` DESC
TehShrike
  • 9,855
  • 2
  • 33
  • 28