Re: Can this be done in one SELECT statement ?

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. > > > --