Re: Can this be done in one SELECT statement ?
Works great!!!
Thanks
N.
"Christopher J. Erdal" <chris@No-Spam-erdal.net> wrote in message
news:Xns937CB8A817C5214torcatis@62.4.16.96...
> This works in MS SQLServer 6.5 for one of my automobile tables:
>
> select S0.momarque, S0.mocode, S0.monom
> from modeles_fam S0
> group by S0.momarque, S0.mocode, S0.monom
> having (select count(distinct mocode)
> from modeles_fam S1
> where (S1.momarque = S0.momarque)
> and (S1.mocode < S0.mocode)
> ) = 0>
> So, replacing my fields with yours would give:
>
> select S0.ID, S0.item_no, S0.description
> from my_table S0
> group by S0.ID, S0.item_no, S0.description
> having (select count(distinct item_no)
> from my_table S1
> where (S1.ID = S0.ID)
> and (S1.item_no < S0.item_no)
> ) = 0>
> N.B. I borrowed some logic from Joe Celko's
> "SQL for smarties" to get here ;-)
>
> Chris.
>
> "N" <N@N.COM> wrote in
> news:YSwwa.368$yM7.41972899@newssvr13.news.prodigy.com:
>
> > Hi,
> > I would like to write a SELECT statement that would return
> > the top row for each group of ID.
> > Ex.
> >
> > ID item_no. description
> > 1 01 XXX
> > 1 02 YYY
> > 1 03 ZZZ
> > 2 02 AAA
> > 2 03 BBB
> > 3 aa MMM
> > 3 bb NNN
> >
> > The SELECT statement should return :
> >
> > ID item_no description
> > 1 01 XXX
> > 2 02 AAA
> > 3 aa MMM
> >
> > Noted that item_no column is not an integer column. I would
> > like to get the first top row that the "ORDER BY" clause returns
> > for each ID.
> > Is it possible to write a single SELECT to accomplish this ?
> >
> > Thanks
> >
> > N.
> >
> >
> >
>
>
>
> --