JayCallas@hotmail.com (Jason) wrote in message news:<f01a7c89.0310061203.24c943e@posting.google.c om>...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:<4uydnUP5vLe0C-CiRVn-tQ@giganews.com>...
> > 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
> >

Hit another small issue. 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).