Consider a table as bolow.> > ID CODE VALUE> > -------------------­--> > 1 a 1> > 2 a 2> > 3 b 1> >

I want the row with the highest 'value' of each 'code'> >

I need the whole row (all fields), and although the ID field is unique> > in this example, it isn't in the real life table, removing the ability> > to do a subquery on ID.> >

Here's a script to build the example table.> >

Thanks,> > Ger> >

--=================­====================­=====> > DROP table #temp> >

SELECT 1 AS 'id', 'a' AS code, 1 AS value> > INTO #temp> > UNION> > SELECT 2, 'a', 2> > UNION> > SELECT 3, 'b', 1> >

SELECT * from ##temp> > --=================­====================­=====> >