3
SELECT empno, deptno
dense_rank() OVER (PARTITION BY deptno
     ORDER BY sal NULLS LAST) SRLNO
FROM emp
WHERE deptno IN (10, 20)
group by empno, deptno  --,sal
ORDER BY deptno, SRLNO;

This Query didn't work because Sal should be in group by clause. Can anyone explain why it is so, and is there any alternative to get the rank in the same select without changing the group by clause? U want to order rank only based on salary.

EDIT
Suppose there is another column name commision in emp table and i have to group by deptno and commision and partition by deptno only ,So what will be the suggested answer :

    SELECT empno, deptno,sum(sal) 
    dense_rank() OVER (PARTITION BY deptno
     ORDER BY sal NULLS LAST) SRLNO
    FROM emp
    WHERE deptno IN (10, 20)
    group by  deptno,commision  --,sal
    ORDER BY deptno, SRLNO;

now how can i group by depno and commision and only partition by deptno.

HOW CAN I GROUP BY ON DIFFERENT COLUMN AND FIND RANK BASED ON PARTITION BY ON DIFFERNT COLUMN

John Doyle
  • 7,475
  • 5
  • 33
  • 40
Gaurav Soni
  • 6,278
  • 9
  • 52
  • 72

1 Answers1

5

Don't group by anything--your partition by does it for you!

More specifically, since you're referencing sal in your partition by and order by, group by needs to be aware of it to group the results bu that. However, in this case, you don't need the group by because your dense_rank is using its own partitioning from the partition by clause, and will not follow whatever is in group by.

Regarding your edit, use your over clause there:

sum(sal) over (partition by deptno, commission) as salsum

Eric
  • 92,005
  • 12
  • 114
  • 115
  • :Thanks for the solution ,but how can i group by with different columns and to find the rank based on different partition ? – Gaurav Soni Jan 11 '12 at 19:04
  • @user1138658 - As I edited, you'll want to use `over` all the way down if you have different granularities. Once you head into the `over` world, you want to avoid `group by` (it can be done, it's just really tricky). – Eric Jan 11 '12 at 19:06