英文:
Get the value associated with maximum per group
问题
I understand your request. Here's the translation of the text you provided:
我在处理一些非常简单的事情，但它让我发疯。我正在使用MySQL和PHPMyadmin。
基本上，我有一个包含URL、ID（唯一）和来源的表格。
每个来源有时会有多个URL。
我只想要每个来源的最长URL。
我有类似以下的东西：
| pid | source | url |
|---|---|---|
| 1 | 127 | a-url |
| 2 | 127 | a-longer-url |
| 3 | 128 | some-kind-of-url |
| 4 | 128 | some-url |
我试图显示：
| pid | source | url |
|---|---|---|
| 2 | 127 | a-longer-url |
| 3 | 128 | some-kind-of-url |
所以，只需获取每个来源的最长URL。
我一直在尝试类似以下的内容：
SELECT pid, source, MAX(LENGTH(url)), url FROM table GROUP BY source
但尽管它显示正确的最大长度数字，我无法获取正确的URL字符串，因为它显示的不是最长的那个。
非常感谢您的帮助。
英文:
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.
答案1
得分: 0
你可以使用窗口函数来按照它们的url长度来排列具有相同source的行，然后选择每个组中的顶部行：
select *
from (
select t.*, rank() over(partition by source order by char_length(url) desc) rn
from mytable t
) t
where rn = 1
这将返回顶部的并列项，如果有的话；否则，在窗口函数的order by子句中，你需要添加一个额外的排序条件。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论