> 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