SELECT dept_id,
year,
Max(marks) marks
FROM offc.col
GROUP BY dept_id,
ORDER BY dept_id,
year
我得到的数据是:
这里没有问题,因为我的sql正在运行right.So,我需要提取
col
表的所有信息,所以我将子查询用作:
代码语言:
javascript
复制
SELECT *
FROM offc.col
WHERE ( dept_id, year, marks ) IN (SELECT dept_id,
year,
Max(marks) marks
FROM offc.col
GROUP BY dept_id,
ORDER BY dept_id,
year);
select *
from (
select
dense_rank() over(partition by dept_id, year order by marks desc nulls last) as dr
from offc.col c
where dr = 1
order by dept_id, year
另外,您的查询是正确的,只需从其中移除顺序即可。
代码语言:
javascript
复制
SELECT *
FROM offc.col
WHERE ( dept_id, year, marks ) IN (SELECT dept_id,
year,
Max(marks) marks