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 [EMAIL PROTECTED]
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
