Getting a sorted distinct list from mySQL
- by Stomped
Goal
I'l like to get a list of unique FID's ordered by the the one which has most recently been changed. In this sample table it should return FIDs in the order of 150, 194, 122
Example Data
ID FID changeDate
----------------------------------------------
1 194 2010-04-01
2 122 2010-04-02
3 194 2010-04-03
4 150 2010-04-04
My Attempt
I thought distinct and order by would do the trick. I initially tried:
SELECT distinct `FID` FROM `tblHistory` WHERE 1 ORDER BY changeDate desc
# Returns 150, 122, 194
using GROUP BY has the same result. I'm just barely a SQL amateur, and I'm a bit hung up. What seems to be happening is the aggregating functions find the first occurrence of each and then perform the sort.
Is there a way I can get the result I want straight from mySQL or do I have to grab all the data and then sort it in the PHP?
