From another post I was given a solution to a problem I was having
with creating a composite view of similiar rows.
http://groups.google.com/groups?dq=&...s.ms-sqlserver
I hit a small issue with the following select statement:
SELECT S.symbol,
COALESCE(T.xidentity,S.xidentity), COALESCE(T.idsource,S.idsource),
COALESCE(T.exchange,S.exchange), COALESCE(T.type,S.type),
COALESCE(T.subtype,S.subtype), COALESCE(T.xname,S.xname)
FROM Stocks AS S
JOIN
(SELECT symbol,
CASE COUNT(DISTINCT NULLIF(xidentity,''))
WHEN 1 THEN MAX(xidentity) END,
CASE COUNT(DISTINCT NULLIF(idsource,''))
WHEN 1 THEN MAX(idsource) END,
CASE COUNT(DISTINCT NULLIF(exchange,''))
WHEN 1 THEN MAX(exchange) END,
CASE COUNT(DISTINCT NULLIF(type,''))
WHEN 1 THEN MAX(type) END,
CASE COUNT(DISTINCT NULLIF(subtype,''))
WHEN 1 THEN MAX(subtype) END,
CASE COUNT(DISTINCT NULLIF(xname,''))
WHEN 1 THEN MAX(xname) END
FROM Stocks
GROUP BY symbol) AS T
(symbol, xidentity, idsource, exchange, type, subtype, xname)
ON S.symbol = T.symbol
For SOME (in this case [type]) columns I need to set a priority. If
two rows have conflicting data (where COUNT > 1) on a particular
column, I want to use the value from the first row in the set. (I
would make sure that rows get inserted in the order I of priority.) I
thought of using TOP 1 somehow but cannot figure out how to replace
the MAX function with it (I know MAX is a function while TOP is a
statement).
