SQL query using UNIONS and GROUP BY

Question

The Query:

SELECT max(image_url), Model_Name
FROM (
    SELECT max(image_url), Model_Name
    FROM dbo.proinfo_vodafone WHERE Brand_name='Nokia'
    UNION
    SELECT max(image_url), Model_Name
    FROM dbo.proinfo_3 WHERE Brand_name='Nokia' 
    UNION
    SELECT max(image_url), Model_Name
    FROM dbo.proinfo_t WHERE Brand_name='Nokia'
    UNION
    SELECT max(image_url), Model_Name
    FROM dbo.proinfo_o2 WHERE Brand_name='Nokia'
    UNION
    SELECT max(image_url), Model_Name 
    FROM dbo.proinfo_orange
    WHERE Brand_name='Nokia') AS temp
WHERE Model_Name IS NOT NULL
GROUP BY Model_Name
ORDER BY Model_name ASC

Is giving the error: Msg 8155, Level 16, State 2, Line 3 No column was specified for column 1 of 'temp'.

Now, to be honest I'm not good with SQL, and I made this query by using Quick Replace in a similar query and I can't seem to understand what is wrong with the query.

Please Help! Thanks in Advance.

Anchit

Solution

  SELECT MAX(image_url), Model_Name
  FROM   (SELECT MAX(image_url) image_url, Model_Name
           FROM   dbo.proinfo_vodafone
           WHERE  Brand_name = 'Nokia'
           UNION
           SELECT MAX(image_url) image_url, Model_Name
           FROM   dbo.proinfo_3
           WHERE  Brand_name = 'Nokia'
           UNION
           SELECT MAX(image_url) image_url, Model_Name
           FROM   dbo.proinfo_t
           WHERE  Brand_name = 'Nokia'
           UNION
           SELECT MAX(image_url) image_url, Model_Name
           FROM   dbo.proinfo_o2
           WHERE  Brand_name = 'Nokia'
           UNION
           SELECT MAX(image_url) image_url, Model_Name
           FROM   dbo.proinfo_orange
           WHERE  Brand_name = 'Nokia') AS temp
  WHERE  Model_Name IS NOT NULL
  GROUP  BY Model_Name
  ORDER  BY Model_name ASC

Comments

[StuartLC]

Yes, alias the MAX() column to something

[Anchit]

Sir, there's no aggregate function for model_name in your subquries, also what will MAX(image_url) image_url compute? :\

[Anchit]

Yes, sir, now I know what I was missing, I have used your query though I have also put a GROUP BY Model_Name clause in each subquery, it works fine now. Though I would now create a new table using this query as suggested by Mr.Peter Lang in a comment to my question, this would save the bandwidth required for applying the unions everytime.