This question already has answers here:
Fetch the rows which have the Max value for a column for each distinct value of another column (35 answers)
GROUP BY with MAX(DATE) [duplicate] (6 answers)
Select First Row of Every Group in sql [duplicate] (2 answers)
Oracle SQL query: Retrieve latest values per group based on time [duplicate] (2 answers)
Get value based on max of a different column grouped by another column [duplicate] (1 answer)
Closed 2 hours ago.
I have this table that shows the same id with different types and every type have the same rank that ordered by start date.
| id | type | start date | rank |
| ------------ | ------------ | ------------ | ------------ |
| 111 | 15 | 1/1/22 | 1 |
| 111 | 15 | 1/3/22 | 1 |
| 111 | 15 | 2/04/22 | 1 |
| 111 | 23 | 1/02/22 | 2 |
| 111 | 23 | 1/3/22 | 2 |
| 111 | 25 | 16/03/22 | 3 |
I want to get table that will show only the last row for every rank
| id | type | start date | rank |
| ------------ | ------------ | ------------ | ------------ |
| 111 | 15 | 2/04/22 | 1 |
| 111 | 23 | 1/3/22 | 2 |
| 111 | 25 | 16/03/22 | 3 |
3条答案
按热度按时间vsmadaxz1#
使用
group by
并获取结果6tdlim6h2#
您可以像这样使用
ROW_NUMBER()
:SQL小提琴
注意:您可以在
partition by
(如"start date", "rank"
)之后以及order by
之后添加任意列组合rta7y2nd3#
我们可以在子查询中使用
FIRST_VALUE
和DISTINCT
,并使用ROWNUM
添加rank列:请注意,如果date列可以是
NULL
,则此查询将失败(与其他答案中的查询一样)!为了避免这种情况,我们可以添加一个IGNORE NULLS
子句:我不知道您描述中的“ranking”列是否已经包含了正确的行号信息,是否可以安全地用于此目的,或者这是否不可能或风险太大。
如果可以使用它,就不需要子查询来获取行号。同样,有两个选项可以忽略或不忽略
NULL
值:或