英文:
SQL Sort shortest and longest first name by last name
问题
我想对我的表格中的名字按姓氏排序，并在两个不同的列中过滤出最短和最长的名字，有很多人的姓氏相同，比如Max Bob、Mini Bob、Howard Bob等等。我只想要相应姓氏的最短和最长的名字。
示例：
| 姓氏 | 最短名字 | 最长名字 |
|---|---|---|
| 米奇 | 麦克斯 | 罗纳德 |
| 鸭 | 鲍勃 | 霍华德 |
| 鲍勃 | 麦克斯 | 霍华德 |
到目前为止，我有：
SELECT
last_name,
MIN(first_name) as shortest_first_name,
MAX(first_name) as longest_first_name
FROM
hr.employees
GROUP BY
last_name;
这会给我每个姓氏的最短和最长名字。
英文:
I would like to sort the first names of my table by their last name and filter out both the shortest and longest first name in two different columns, there are several people with the same last name like Max Bob, Mini Bob, Howard Bob and so on. I only want the shortest and longest first name for the corresponding last name
Example:
| last name | shortest first name | longest first name |
|---|---|---|
| Mickey | Max | Ronald |
| Duck | Bob | Howard |
| Bob | Max | Howard |
etc.
So far I have:
SELECT
COUNT(last_name) as last name,
MIN(first_name) as shortest,
MAX(first_name) as longest
FROM
hr.employees
group by first_name;
which gives me the count of the last_name but not the last_name itself
| last name | shortest first name | longest first name |
|---|---|---|
| 1 | Max | Max |
| 1 | Howard | Howard |
| 2 | Max | Max |
| 2 | Howard | Howard |
but I'm stuck on how to proceed, any advice?
答案1
得分: 3
在Oracle中，我们可以在这里使用keep语法：
select last_name,
min(first_name) keep(dense_rank first order by length(first_name)) shortest_last_name,
min(first_name) keep(dense_rank last order by length(first_name)) longest_last_name
from employees
group by last_name;
如果有多个最短或最长的名字，查询会选择字符串方面的“最小”值。
以下是一个演示：
with employees as (
select 'Mickey' as last_name, 'Max' as first_name from dual
union all select 'Mickey', 'Ronald' from dual
union all select 'Mickey', 'Bill' from dual
union all select 'Duck', 'Bob' from dual
union all select 'Duck', 'Howard' from dual
union all select 'Bob', 'Max' from dual
union all select 'Bob', 'Howard' from dual
)
select last_name,
min(first_name) keep(dense_rank first order by length(first_name)) shortest_last_name,
min(first_name) keep(dense_rank last order by length(first_name)) longest_last_name
from employees
group by last_name;
| LAST_NAME | SHORTEST_LAST_NAME | LONGEST_LAST_NAME |
|---|---|---|
| Bob | Max | Howard |
| Duck | Bob | Howard |
| Mickey | Max | Ronald |
英文:
In Oracle, we could use the keep syntax here:
select last_name,
min(first_name) keep(dense_rank first order by length(first_name)) shortest_last_name,
min(first_name) keep(dense_rank last order by length(first_name)) longest_last_name
from employees
group by last_name;
If there are ties for the shortest or longest first name, the query picks the "minimum" value, string-wise.
Here is a demo:
with employees as (
select 'Mickey' as last_name, 'Max' as first_name from dual
union all select 'Mickey', 'Ronald' from dual
union all select 'Mickey', 'Bill' from dual
union all select 'Duck', 'Bob' from dual
union all select 'Duck', 'Howard' from dual
union all select 'Bob', 'Max' from dual
union all select 'Bob', 'Howard' from dual
)
select last_name,
min(first_name) keep(dense_rank first order by length(first_name)) shortest_last_name,
min(first_name) keep(dense_rank last order by length(first_name)) longest_last_name
from employees
group by last_name;
| LAST_NAME | SHORTEST_LAST_NAME | LONGEST_LAST_NAME |
|---|---|---|
| Bob | Max | Howard |
| Duck | Bob | Howard |
| Mickey | Max | Ronald |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论