Organizational Research By

Surprising Reserch Topic

selecting all corresponding fields using max and group by


selecting all corresponding fields using max and group by  using -'sql,mysql,group-by,max,greatest-n-per-group'

I have this table :



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

    

asked Sep 22, 2015 by nimisha.jagtap
0 votes
13 views



Related Hot Questions

2 Answers

0 votes

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

answered Sep 22, 2015 by 20shahi
0 votes

Hi i hope this gives what u want it

select deal_id,status_id, timestamp 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 order by deal_id
answered Sep 22, 2015 by rolvyrf

...