I've already replied to this under your original thread. No need to repost.

--
David Portas
------------
Please reply only to the newsgroup
--

"Jason" <JayCallas@hotmail.com> wrote in message
news:f01a7c89.0310080543.657a9360@posting.google.c om...
> 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).