Group By on a Union

[SnapJack]

Hi,

I have a problem in sql server 2000.

Take the example below and please let me know how i could
group by and order by ponumber and company
(Select ponumber,Company from tableA)
union
(Select ponumber,Company from tableB)

Please help asap as this urgent.

 

[sqltimmy]

If you want to union them and THEN order them, I think this would work:

SELECT *
FROM
(Select ponumber,Company from tableA
union
Select ponumber,Company from tableB) AS TABLE1
ORDER BY ponumber

Is that what you mean? I didn't understand what you meant by "group by and order by ponumber and company". I'm not sure how you could order by ponumber AND company at the same time, but the SQL above should let you order by one of them.

 

[tlbroadbent]

Simply remove the parenthesis and add the group by and ordery clauses at the end.

Select ponumber,Company
from tableA
union
Select ponumber,Company
from tableB
group by ponumber,Company
Order by ponumber,Company

Of course, in this simlple query you will get the same result if you omit the goup by and order by clauses because SQL Server sorts the result set to eliminate duplicates when performing a UNION Query.

Select ponumber,Company
from tableA
union
Select ponumber,Company
from tableB Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.