0

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
Sébastien Sutterlin
  • 43
  • 3

1 Answers1

0

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Hello, thanks for your answer ! (And for the editing btw, somehow the presentation got screwed when I posted, the preview was fine) Sadly, I don't have access to Window functions ! I managed to find a workaround during night though. I did this: INSERT IGNORE INTO new_order (source,url) SELECT DISTINCT source,url AS aliases FROM url_alias ORDER BY LENGTH(alias) DESC By doing so I got a table with all rows in order of length. Then I inserted it in a new table "New order" and took the first value per source. Thanks for your help ! – Sébastien Sutterlin Jun 22 '23 at 09:00