Getting a summary of the newest entries from a history table

Hello,

I am stuck at the following task: There is a table with the following structure:

history(item_id, cdate, stats_loads, stats_delay, stats_requests, other_stats)

Example data:
item_id, stats_loads, cdate
1, 255, 2013-04-11
1, 134, 2013-04-10
1, 135, 2013-04-09
2, 164, 2013-04-10
2, 125, 2013-04-09
3, 215, 2013-04-11
3, 334, 2013-04-10
3, 195, 2013-04-09
3, 15, 2013-04-08


The query should efficiently produce a summary table containing one row per item with statistics from the latest date. Not every item is used everyday, thus we cannot use something like WHERE cdate > NOW(), it rather should return the latest entry like ORDER BY cdate DESC LIMIT 1.

From the sample data this query sholud produce the following result:
item_id, stats_loads, cdate
1, 255, 2013-04-11
2, 164, 2013-04-10
3, 215, 2013-04-11

Note, the example is reduced to one column stats_loads. However, there are more columns to show like stats_delay and stats_requests.

The only approach I can think of is building a subquery for each stats-column but this appears to me extremely inefficient.

Any suggestions are welcome!

 

If you group by the "item" not sure which column this is and then sort by the cdate DESC, you should get the most recent for each item.

I believe it should be:

SELECT
  item_id,
  stats_loads,
  cdate,
  stats_delay,
  stats_requests,
  other_stats
FROM history_table
GROUP BY item_id
ORDER BY cdate DESC;

Code (markup):

 

If this would be that easy, I would not have to answer.
item_id would be the right column for GROUP BY - but it does not work this way. It groups by first (without sorting the date), than you are having one row per item with the date from one of the corresponding rows (I do not get the pattern behind it), and than you ORDER BY this random date. Does not work, unfortunatelly.

The only solution that works for me is inefficient one. It consists of a subquerry, which for each item_id returns the first column of the by-date-sorted list for this item_id.