Can this be done in one SELECT statement ?

[N]

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.

[Christopher J. Erdal]

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.41...@newssvr13.news.prodigy.com:

--

[N]

Works great!!!
Thanks
N.
"Christopher J. Erdal" <ch...@No-Spam-erdal.net> wrote in message
news:Xns937CB8A817...@62.4.16.96...