specifying date to order by in union query

Question

I'm doing a union of two different tables and ordering by the date column. Shouldn't the union make the two date columns one? It's giving me the error: #1052 - Column 'date' in order clause is ambiguous

Am I missing something here? Here's the query:

SELECT comments.*, postid, prayers.date AS date, prayers.type AS type
       FROM comments 
       LEFT JOIN prayers USING (postid) 
       WHERE comments.username = 'hoodleehoo'
       AND comments.new = 1
       AND comments.first = 1
       AND (comments.type = 'prayer' or comments.type = 'answer')
       AND prayers.privacy != 'hidden'
       UNION
       SELECT comments.*, postid, posts.date AS date, comments.type AS type
       FROM comments
       LEFT JOIN posts USING (postid)
       WHERE comments.username = 'hoodleehoo'
       AND comments.new = 1
       AND comments.first = 1
       AND (comments.type = 'post' or comments.type = 'shared')
       ORDER BY date

UPDATE:

I guess you can't use "date". That must be a reserved word. I changed date to "date2" and it's working fine. I'm sure I'm not the only one who will run into this!

you don't need to change column name you can use [date] – wiretext Jul 24 '15 at 14:18 — wiretext, Jul 24 '15 at 14:18

score 1 · Answer 1 · edited Jul 24 '15 at 15:47

1

For your query to work as it is now the last line should be ORDER BY posts.date. And by your question i'm thinking you are actually trying to do this

SELECT * FROM ( SELECT comments.*, postid, prayers.date AS DATE2, prayers.type AS type
               FROM comments 
               LEFT JOIN prayers USING (postid) 
               WHERE comments.username = 'hoodleehoo'
               AND comments.new = 1
               AND comments.first = 1
               AND comments.type = 'prayer'
               AND prayers.privacy != 'hidden'
               UNION
               SELECT comments.*, postid, posts.date AS DATE2, comments.type AS type
               FROM comments
               LEFT JOIN posts USING (postid)
               WHERE comments.username = 'hoodleehoo'
               AND comments.new = 1
               AND comments.first = 1
               AND comments.type != 'prayer')
               ORDER BY DATE2

edited Jul 24 '15 at 15:47

thinkofacard

491
1
6
19

answered Jul 24 '15 at 14:10

Kibadachi

155
6

But it's a union. I need those two date columns to be made into one and order by both of them together. – thinkofacard Jul 24 '15 at 14:13
I tried doing "as date" on both but it still gave me the same error. I updated the original question with a pasted version of the actual query I'm trying. – thinkofacard Jul 24 '15 at 14:14
the error came from the last line ORDER BY date cause it need the table `posts` as a prefix – Kibadachi Jul 24 '15 at 14:16

wiretext · Answer 2 · 2015-07-24T14:18:03.830

0

you have to specify tablename with date column

ORDER BY tablename.[date]

edited Jul 24 '15 at 14:18

answered Jul 24 '15 at 14:10

wiretext

3,302
14
19

score 0 · Answer 3 · edited May 23 '17 at 12:14

0

The "ambiguous" messaging is an aliasing issue. As for ordering the results of a union. It is answered here: How to order by with union

Check Mark Robinson's answer. I think it will suit your needs.

edited May 23 '17 at 12:14

Community

1
1

answered Jul 24 '15 at 14:16

Tyler Brown

135
2

specifying date to order by in union query

3 Answers3