Logic for displaying below row as first row [message #653801] |
Tue, 19 July 2016 04:35 |
|
sss111ind
Messages: 636 Registered: April 2012 Location: India
|
Senior Member |
|
|
Hi All,
Two new columns cnt_1,cnt2 will appear whenever new rec_id=1 found as first value of rec_id =1,rec_id=2
For example for first rec_id=1 the value for cnt_1 =3, cnt_2=2
for second rec_id=1 the the value for cnt_1=5 ,cnt_2=1.
Please help me.
SELECT TT.* FROM (
SELECT T.* ,COUNT(*) OVER(PARTITION BY DEPTNO,REC_ID ORDER BY DEPTNO,REC_ID) CNT
FROM (
SELECT 1 REC_ID, EMP.* FROM emp
WHERE DEPTNO IN ('10','20')
UNION ALL
SELECT 2 REC_ID,EMP.* FROM emp
WHERE DEPTNO IN ('10','20')
AND JOB IN ('MANAGER','PRESIDENT'))T)TT;
Regards,
Nathan
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Logic for displaying below row as first row [message #653843 is a reply to message #653805] |
Wed, 20 July 2016 03:18 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The following is ordered by deptno and rec_id, but each time that you run the query you may get any order of empno, ename, and job within each group of deptno and rec_id. If you decide that you want to order by one or more of those or some other column, then you can just add that to the ORDER BY within the ROW_NUMBER.
SCOTT@orcl_12.1.0.2.0> SELECT rec_id, empno, ename, job, deptno, cnt,
2 CASE WHEN rec_id = 1 AND rn = 1 THEN cnt1 END cnt_1,
3 CASE WHEN rec_id = 1 AND rn = 1 THEN cnt2 END cnt_2
4 FROM (SELECT rec_id, empno, ename, job, deptno,
5 COUNT (*) OVER (PARTITION BY deptno, rec_id ORDER BY deptno, rec_id) cnt,
6 COUNT (DECODE(rec_id, 1, 1, NULL)) OVER (PARTITION BY deptno) cnt1,
7 COUNT (DECODE(rec_id, 2, 1, NULL)) OVER (PARTITION BY deptno) cnt2,
8 ROW_NUMBER () OVER (PARTITION BY deptno, rec_id ORDER BY deptno, rec_id) rn
9 FROM (SELECT 1 rec_id, empno, ename, job, deptno
10 FROM emp
11 WHERE deptno IN ('10','20')
12 UNION ALL
13 SELECT 2 rec_id, empno, ename, job, deptno
14 FROM emp
15 WHERE deptno IN ('10','20')
16 AND job IN ('MANAGER', 'PRESIDENT')))
17 ORDER BY deptno, rec_id, rn
18 /
REC_ID EMPNO ENAME JOB DEPTNO CNT CNT_1 CNT_2
---------- ---------- ---------- --------- ---------- ---------- ---------- ----------
1 7934 MILLER CLERK 10 3 3 2
1 7782 CLARK MANAGER 10 3
1 7839 KING PRESIDENT 10 3
2 7782 CLARK MANAGER 10 2
2 7839 KING PRESIDENT 10 2
1 7902 FORD ANALYST 20 5 5 1
1 7788 SCOTT ANALYST 20 5
1 7566 JONES MANAGER 20 5
1 7369 SMITH CLERK 20 5
1 7876 ADAMS CLERK 20 5
2 7566 JONES MANAGER 20 1
11 rows selected.
|
|
|
|