Getting first row

[Jason]

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=&hl=en&lr=&ie=UTF-8&threadm=f01a7c89.0310070756.6434ecfd%40posting.google.com&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26group%3Dcomp.databases.ms-sqlserver

I hit a small issue with the following select statement:

SELECT S.symbol,
COALESCE(T.xide ntity,S.xidenti ty), COALESCE(T.idso urce,S.idsource ),
COALESCE(T.exch ange,S.exchange ), COALESCE(T.type ,S.type),
COALESCE(T.subt ype,S.subtype), COALESCE(T.xnam e,S.xname)
FROM Stocks AS S
JOIN
(SELECT symbol,
CASE COUNT(DISTINCT NULLIF(xidentit y,''))
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).

[David Portas]

Re: Getting first row

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

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

"Jason" <JayCallas@hotm ail.com> wrote in message
news:f01a7c89.0 310080543.657a9 360@posting.goo gle.com...[color=blue]
> From another post I was given a solution to a problem I was having
> with creating a composite view of similiar rows.
>
>[/color]
http://groups.google.com/groups?dq=&...s.ms-sqlserver[color=blue]
>
> I hit a small issue with the following select statement:
>
> SELECT S.symbol,
> COALESCE(T.xide ntity,S.xidenti ty), COALESCE(T.idso urce,S.idsource ),
> COALESCE(T.exch ange,S.exchange ), COALESCE(T.type ,S.type),
> COALESCE(T.subt ype,S.subtype), COALESCE(T.xnam e,S.xname)
> FROM Stocks AS S
> JOIN
> (SELECT symbol,
> CASE COUNT(DISTINCT NULLIF(xidentit y,''))
> 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).[/color]

[Jason]

Re: Getting first row

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message news:<UeCdnZUl2 PfduBmiRVn-hA@giganews.com >...[color=blue]
> I've already replied to this under your original thread. No need to repost.
>
> --
> David Portas
> ------------
> Please reply only to the newsgroup
> --
>
> "Jason" <JayCallas@hotm ail.com> wrote in message
> news:f01a7c89.0 310080543.657a9 360@posting.goo gle.com...[color=green]
> > From another post I was given a solution to a problem I was having
> > with creating a composite view of similiar rows.
> >
> >[/color]
> http://groups.google.com/groups?dq=&...s.ms-sqlserver[color=green]
> >
> > I hit a small issue with the following select statement:
> >
> > SELECT S.symbol,
> > COALESCE(T.xide ntity,S.xidenti ty), COALESCE(T.idso urce,S.idsource ),
> > COALESCE(T.exch ange,S.exchange ), COALESCE(T.type ,S.type),
> > COALESCE(T.subt ype,S.subtype), COALESCE(T.xnam e,S.xname)
> > FROM Stocks AS S
> > JOIN
> > (SELECT symbol,
> > CASE COUNT(DISTINCT NULLIF(xidentit y,''))
> > 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).[/color][/color]

My bad. For some reason I never saw MY post. Thought it never made it
to newsgroup. (Wish there was way to cancel post).

Thanks for answer Dave.