Selecting all corresponding fields using MAX and GROUP BY

sql mysql group-by max greatest-n-per-group
afewcc picture afewcc · Aug 20, 2009 · Viewed 12.9k times · Source

I have this table :

alt text

And I would like to make a request that would return for each deal_id the row with the highest timestamp, and the corresponding status_id.

So for this example, I would have returned 2 rows :

1226, 3, 2009-08-18 12:10:25
1227, 2, 2009-08-17 14:31:25

I tried to do it with this query

SELECT deal_id, status_id, max(timestamp) FROM deal_status GROUP BY deal_id

but it would return the wrong status_id :

1226, 1, 2009-08-18 12:10:25
1227, 1, 2009-08-17 14:31:25

Answer

Zed picture Zed · Aug 20, 2009

without a single primary key field, I think your best bet is:

select * from deal_status
inner join
  (select deal_id as did, max(timestamp) as ts
  from deal_status group by deal_id) as ds
  on deal_status.deal_id = ds.did and deal_status.timestamp = ds.ts

this still won't work if you allow having two different statuses for the same product at the same time

Related questions

SQL Query to get column values that correspond with MAX value of another column?

Ok, this is my query: SELECT video_category, video_url, video_date, video_title, short_description, MAX(video_id) FROM videos GROUP BY video_category When it pulls the data, I get the correct row for the video_id, but …

Read More
Retrieving the last record in each group - MySQL

There is a table messages that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 If I run a query select * …

Read More
MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More