I have a MySQL database with multiple columns. Entries can appear in columns several times (except for the primary key). I would like to be able to return for each corresponding separate record in one column the entire row containing the first occurrence of this record from the table, sorted by the second column. Consider a table like:
---------------------------------------
| id | item | cost | location | ... |
---------------------------------------
| 1 | apple | 10 | kitchen | ... |
| 2 | apple | 20 | fridge | ... |
| 3 | banana | 20 | kitchen | ... |
| 4 | carrot | 30 | fridge | ... |
| 5 | apple | 10 | barn | ... |
---------------------------------------
So, sorting by cost (then id) and selecting a single item, I would return rows 1, 3 and 4. (The cheapest cost for each item.) Sort by location (then id) and selecting a single item, I will return 5, 4 , 3. (First alphabetical location for each item.)
, , , , , , . , , , , . , .
? ( MySQL 5.1.53 PHP 5.2.11.)