Home » SQL & PL/SQL » SQL & PL/SQL » Displaying all columns in SELECT statement, but group by few (Oracle 10g, Win XP)
| Displaying all columns in SELECT statement, but group by few [message #402687] |
Tue, 12 May 2009 01:40  |
|
|
Hi,
I have a scenario,
I have a table ( Say eg. SCOTT.EMP), i want to display Empno,Ename,Sal,Job,Hiredate,Deptno columns but need to group by only on Deptno and Job, how do i do it ?
Select Empno,Ename,Sal,Job,Hiredate,Deptno from emp group by Deptno,Job
The above query will obviously give error(ORA-00979: not a GROUP BY expression). Do i need to use Analytical function to achieve it ?
I tried the following, but i don't think this is the feasable solution,
select empno,ename,sal,hiredate,deptno,job from (SELECT empno, ename, sal, hiredate,deptno,job,
ROW_NUMBER () OVER ( PARTITION by deptno ORDER BY deptno) emp_deptno,
ROW_NUMBER () OVER (PARTITION BY job ORDER BY job) emp_job
FROM emp) order by deptno,job
Please note i am not using any Aggregated functions.
Regards,
Ashoka BL
|
|
|
|
|
|
|
|
| Re: Displaying all columns in SELECT statement, but group by few [message #402695 is a reply to message #402687] |
Tue, 12 May 2009 01:57  |
|
|
@Frank,
Assume if i want only one record( may be top-1) for the Deptno,Job Combination, Do i need to use analytical function for it like following ??
SELECT empno, ename, sal, hiredate,deptno,job,
ROW_NUMBER () OVER ( PARTITION by deptno ORDER BY deptno) emp_deptno,
ROW_NUMBER () OVER (PARTITION BY job ORDER BY job) emp_job
FROM emp
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Dec 07 03:29:58 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
