help with query getting max number per row with multiple rows

[raphael75]

I have data that looks like this:

id	escalation_id	escalation_user_id	level	dt_added	status_type_id
126	92	390	0	12/02/15 01:52 PM	4
127	93	390	0	12/02/15 01:53 PM	4
128	93	77	1	12/02/15 01:57 PM	1
129	94	390	0	12/02/15 01:58 PM	4
130	94	77	1	12/02/15 02:19 PM	2
131	93	77	2	12/02/15 02:20 PM	1
133	95	390	0	12/02/15 03:47 PM	4
134	96	390	0	12/02/15 03:53 PM	4
135	97	390	0	12/02/15 03:57 PM	4
136	98	390	0	12/02/15 03:59 PM	4
137	99	390	0	12/03/15 07:20 AM	4
138	100	390	0	12/03/15 07:20 AM	4
139	101	390	0	12/03/15 07:20 AM	4
140	102	390	0	12/03/15 07:23 AM	4
141	103	77	0	12/03/15 07:46 AM	4
142	104	194	0	12/03/15 08:11 AM	4
143	105	194	0	12/03/15 03:38 PM	4
144	106	194	0	12/04/15 10:48 AM	4
145	107	194	0	12/07/15 07:04 AM	4
146	107	13	1	12/07/15 07:08 AM	1
147	107	706	2	12/07/15 07:43 AM	1
148	95	77	1	12/08/15 03:23 PM	1
149	108	194	0	12/09/15 08:54 AM	4
150	108	13	1	12/09/15 08:56 AM	1
151	109	194	0	12/09/15 09:08 AM	4
152	109	77	1	12/09/15 09:11 AM	1
153	109	701	2	12/09/15 09:13 AM	1
154	108	706	2	12/09/15 09:36 AM	1
155	110	194	0	12/09/15 09:44 AM	4
156	110	77	1	12/09/15 09:48 AM	1
168	110	77	2	12/09/15 01:24 PM	2

I am trying to create a query that will return the row with the highest `level` for all `escalation_id`'s. I can do it for 1 escalation like this:

select
es.escalation_id, 
es.escalation_user_id, 
es.`level` as 'cur_level',  
es.dt_added, 

es.status_type_id

from
ABCD.t_escalation_status es

where
es.`level` = (select max(`level`) as 'cur_level' from ABCD.t_escalation_status esx where esx.escalation_id = 110 group by esx.escalation_id, esx.`level` order by cur_level desc limit 1)
and
es.escalation_id = 110

returns:

escalation_id	escalation_user_id	cur_level	dt_added	status_type_id
110	18	2	12/09/15 01:24 PM	2

but I can't get it to work for all `escalation_id`'s. Any help is greatly appreciated.

 

[raphael75]

I was able to get it working like this:

select
es.id,
es.escalation_id, 
es.escalation_user_id, 
max(es.`level`) as 'cur_level',  
es.dt_added, 

es.status_type_id


from
ABCD.t_escalation_status es
where
(escalation_id, `level`) in (
select escalation_id, max(`level`) from ABCD.t_escalation_status esx group by esx.escalation_id
)

group by
es.escalation_id

thanks to this thread:

http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column