asked on

Select bigger string from 2 or more strings in a column

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

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

ralmada

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

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dmonzon

ASKER

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

ralmada

Angel, Not sure why my query will not help the asker. Can you please advise?