We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Select bigger string from 2 or more strings in a column

Medium Priority
430 Views
Last Modified: 2012-05-07
I want to make a group seletc that retrieves a distinct string from col1 and the larger string from col2.....
ex.
[col1]           [col2 ]  [col3]
1111          aaa        3
2222          bbb         3
1111          ccccc     5
2222          ddddd     5
3333          eeee       4
3333          f              1

results wanted!!
[col1]   [col2]  
1111    ccccc
2222    ddddd  
3333    eeee

col1 and col3 are string , col3 int with the length of the string in col2.....
i made several mysql query and sometimes retrieves the larger string, others not.

i know how to do this with php but i want to doit in a mysql query, maybe i need a sub query...
Thanks.
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
SELECT Col1, MAX(Col2)Col2
FROM urTable
GROUP BY Col1

Commented:
I think you're looking for this:
select a.col1, a.col2
from yourtable a
join (select col1, max(col3) as mcol3 from yourtable group by col1) b on a.col1 = b.col1 and a.col3 = b.mcol3

Open in new window

awking00Information Technology Specialist
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
sorry for the delay, i already resolve this the same day and i forgot to close the question, any way i will try your answers and let you know .....
thanks

Commented:
Angel, Not sure why my query will not help the asker. Can you please advise?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.