Posted by Erland Sommarskog on 06/18/05 00:56

Ross Presser (rpresser@NOSPAMgmail.com.invalid) writes:
> May I ask why you would choose to go the COALESCE + FULL JOIN route,
> rather than just the MAX + GROUP BY? It would seem much more
> straightforward, and it seems to me it would work even if X is a unique
> key. Is there some advantage to FULL JOIN that I don't understand?

Because there were two tables, and the example included values that
were in 1) Table1 only 2) in Table2 only 3) in both tables. You must
somehow combine these two tables. If X is a unique key, FULL JOIN is
a possible way to go.

Personally my favourite is this the UNION query:

SELECT x, MAX(y)
FROM
(SELECT x,y FROM Table1 UNION ALL
SELECT x,y FROM Table2) AS T
GROUP BY x

It expresses the operation well, it's easy to understand, and you
can easily extended to several tables if that need would appear. And it
does not make any assumptions about the uniqueness of X.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]