CODEWITHSUNDEEP
mysqlsqlselectgroup-by
rc plitt
rc plitt

Reputation: 23

Return max(field) for group of records - no, not just max()

I have a table that logs field data collection, with each data snippet from a group as an individual row (e.g. length and width for an item are each a row in the table).

I would like to display the log results sorted by the timestamp of the most recent log entry, grouped together by the item.

I tried various flavours of max(timestamp) in subselects, joins, etc., but no joy.

Basically, like this (simplified so it's easier to type):

 item  |  timestamp  |  data
  G    |    1000     | yellow
  D    |    1001     |  blue
  C    |    1002     |   red
  G    |    1003     |  green
  D    |    1004     |  pickle
  Z    |    1005     | trotsky

I would like my result set to sort like:

  G    |    1000     | yellow
  G    |    1003     |  green
  D    |    1001     |   blue
  D    |    1004     |  pickle
  C    |    1002     |   red
  Z    |    1005     |  trotsky

which would imply Group By item, Order By timestamp, but of course that doesn't work because I need all rows and that would put item A at the top of the list, instead of the item that was most recently logged.

Obviously I'm missing the real meat of the solution. GROUP BY doesn't work because I still need to return all of the rows, therefore some sort of JOIN seems to make sense, I just need the oracle to learn me how to construct it.

Upvotes: 1

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

It looks like you want to order the items by the min of the timestamp for each item. Try this:

select l.*
from logs l join
     (select item, min(timestamp) as mints
      from logs
      group by item
     ) i
     on l.item = i.item
order by i.mints, l.item, l.timestamp;

As a note: your data screams "order by the min". Your text says "order by the max". Obviously, if you want the max, it is just a simple modification to this query.

Upvotes: 2

Related Questions