Re: SQL group select question

> I 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:

SELECT t.id, t.value
FROM (SELECT name, MAX(numb) as numb FROM test1) AS s
JOIN test1 t ON (t.name = s.name AND t.numb = s.numb);

> id  value
> --  -----
> 6   6000
> 4   4000
> 1   1000

Cheers,

Ezequiel Tolnay
etolnay@gbtech.com.au