sql - max, over partition by, oracle, 取最大 rownum 记录
问题描述
我正在尝试提取分区中的最大 rownum。我收到以下错误消息,因此我需要帮助来修复我的 SQL 查询。我在 SQL 查询的分区中添加了行号和行号。代码如下。我想在这个分区上取最大值,并尝试更改查询几次,但不断收到错误消息,所以我被卡住了。我需要一些帮助。请参阅以下代码和错误消息。
SELECT *,
max(rownum1) OVER (PARTITION BY id) as maxrownum1
from
(
select
id, NAME_TYPE, NAME, EFFDT,
rownum,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY rownum ASC) AS rownum1
FROM name_table
) a
where a.rownum1=maxrownum1
and rownum<=2000
错误信息
ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
*Cause:
*Action:
Error at Line: 1 Column: 9
解决方案
这似乎很好奇。您不能在where
子句中引用select
定义它的别名。因此,将定义放在子查询中:
select t.*,
from (select id, NAME_TYPE, NAME, EFFDT,
rownum as rn, max(rownum) over (partition by id) as max_rownum
from name_table
) nt
where rn <= max_rownum and
rownum <= 2000;
推荐阅读
- angular - 在组件中可访问但在模板中未定义的变量
- javascript - "MapQuest" Coding Challenge - Eliminate "Directions" That Cancel Each Other Out
- python - Trying to flatten a Reddit JSON into many "conversations"
- c++ - variadic template for sorting variable number of arrays
- c++ - “地图”中的方括号是什么意思
实例[数量特征];" - opengl - glMultiDrawElements 和 glDrawElements 等价
- javascript - How to route the form action to a specific file by using express router
- javascript - 控制按钮切换与窗口大小重置
- swift - wanting to fetch firestore like realTimeDatabase
- android - 使用 mediacodec 在 android 上解码和组合两个视频