Get the value associated with maximum per group

I'm struggling on something very simple and it's making me crazy. I'm using MySQL and PHPMyadmin. Basically, I have a table containing urls, ids (unique) and source. Each source has sometime several urls.

I simply want to **longest url per source. **

I have something like this:

pid	source	url
1	127	a-url
2	127	a-longer-url
3	128	some-kind-of-url
4	128	some-url

And I'm trying to display:

pid	source	url
2	127	a-longer-url
3	128	some-kind-of-url

So, simply getting the longer url per source.

I've been trying stuff like:

SELECT pid, source, MAX(LENGTH(url)), url FROM table GROUP BY source

but though it displays the right maximum length in number I cannot fetch the right url string, as it displays another one than the longest.

Thanks a lot for your help.

Solution

You can use window functions to rank rows having the same source by the length of their url, then select the top row(s) per group:

select *
from (
    select t.*, rank() over(partition by source order by char_length(url) desc) rn 
    from mytable t
) t
where rn = 1

This returns top ties, if any; else, you would need an additional sorting criteria in the order by clause of the window function.