CODEWITHSUNDEEP
mysql
thinkofacard
thinkofacard

Reputation: 501

specifying date to order by in union query

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!

Upvotes: 0

Views: 43

Answers (3)

Kibadachi
Kibadachi

Reputation: 175

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

Upvotes: 1

wiretext
wiretext

Reputation: 3342

you have to specify tablename with date column

ORDER BY tablename.[date]

Upvotes: 0

Tyler Brown
Tyler Brown

Reputation: 155

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.

Upvotes: 0

Related Questions