INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

help with query getting max number per row with multiple rows

help with query getting max number per row with multiple rows

help with query getting max number per row with multiple rows

(OP)
I have data that looks like this:

CODE -->

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:



CODE -->

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:


CODE -->

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.

RE: help with query getting max number per row with multiple rows

(OP)
I was able to get it working like this:

CODE -->

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-sel...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close