SQL group select question
am 14.04.2005 00:00:40 von Dave RoeI have a table with a unique id, a name, a number and a value, for example:
id name numb value
-- ---- ---- -----
1 tom 10 1000
2 dick 10 2000
3 harry 10 3000
4 dick 21 4000
5 harry 21 5000
6 harry 32 6000
As you can see, tom has 1 entry, dick has 2 and harry has 3. I would
like to select 3 records (one for each tom, dick and harry), based on
the associated number (id 1 should be chosen, because there is no other
'tom' record, id 4 should be chosen over id 2, because 21 > 10, and id 6
over ids 3 and 5, because 32 > 10 or 21). The value column is the data
that I am interested in:
id value
-- -----
6 6000
4 4000
1 1000
So far, I have tried:
SELECT id, value FROM test1 GROUP BY name, numb DESC
but this, of course, gives me all 6 records, but grouped so that the
first tom, dick or harry is the record I am interested in.
Can anyone offer any pointers?
/dave