Group by ID, select only the newest record for each

Question

Each row in my table is the status of a station at a given time. It has a lot of things that change over time. Water flow, tempature, ect. I want to be able to return the most recent record from each station. Not all of the stations update at the same time so I am not able to just grab the last few records and know that I have them all.

Flow    Sortdate    Station
-----------------------------------------
164     2013-07-19  11502940
520     2013-07-19  11502500
164     2013-07-20  11502940
520     2013-07-20  11502500
164     2013-07-21  11502940
520     2013-07-21  11502500

I would think that this should work.

select station, sortdate, flow from dailymean 

group by  Station
order by sortdate DESC

It does not. I have looked on here and someone had something like this.

select f.station, f.date, f.flow from dailymean f,dailymean second on second.station=f.station 
where f.station>second.station,
group by  f.Station
order by f.sortdate DESC

That will kick out the oldest set of records, but will return all of the others, not just the newest ones.

Just to be clear I need more than just the date it was last updated. I also need the water flow and many other things from the last time that each station was updated. I am using SQLite.

Answer 1

I would do it this way:

SELECT
  dailymean.Station,
  dailymean.Flow,
  dailymean.Sortdate
FROM
  dailymean
  INNER JOIN (
    SELECT Station, MAX(Sortdate) AS maxDate FROM dailymean GROUP BY Station
  ) AS Tmp ON dailymean.Station = Tmp.Station
          AND dailymean.Sortdate = Tmp.maxDate

(Live code here)

Answer 2

Basically what you want is all the columns from the table where the sortdate is maximal for that station. Doing this in sql is a bit messy, as you can see here: MySQL SELECT unique column where other column is max

The query for your schema would be, if I'm not making any mistakes

SELECT *
FROM dailymean
WHERE (station, sortdate) IN
    (SELECT station, MAX(sortdate)
     FROM dailymean
     GROUP BY station)