Home » SQL & PL/SQL » SQL & PL/SQL » Logic for displaying below row as first row (Oracle 10g)
Logic for displaying below row as first row [message #653801] Tue, 19 July 2016 04:35 Go to next message
sss111ind
Messages: 634
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 #653802 is a reply to message #653801] Tue, 19 July 2016 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Question

Re: Logic for displaying below row as first row [message #653805 is a reply to message #653802] Tue, 19 July 2016 05:04 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

The result we want as follows,


REC_ID	        EMPNO	ENAME	JOB		DEPTNO	             CNT	CNT_1	    CNT_2
1		7839	KING	PRESIDENT	10			3	3		2
1		7934	MILLER	CLERK		10			3			
1		7782	CLARK	MANAGER		10			3			
2		7782	CLARK	MANAGER		10			2			
2		7839	KING	PRESIDENT	10			2			
1		7369	SMITH	CLERK		20			5	5		1
1		7876	ADAMS	CLERK		20			5				
1		7788	SCOTT	ANALYST		20			5				
1		7566	JONES	MANAGER		20			5				
1		7902	FORD	ANALYST		20			5				
2		7566	JONES	MANAGER		20			1	

Re: Logic for displaying below row as first row [message #653807 is a reply to message #653805] Tue, 19 July 2016 05:12 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So cnt_1 and cnt_2 should be hard-coded on certain records?
And how are you defining first?
Re: Logic for displaying below row as first row [message #653808 is a reply to message #653805] Tue, 19 July 2016 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The result we want as follows,


What is the order of the rows?

Re: Logic for displaying below row as first row [message #653809 is a reply to message #653808] Tue, 19 July 2016 05:56 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Thanks for your reply.

Cnt_1 and cnt_2 value derived from cnt column for rec_id=1 and rec_id=2 values respectively for each starting deptno fields.
These values appear once for each deptno.
In summary

deptno cnt_1(record found in query1) cnt_2(record found in query2)
10 3 (cnt from rec_id=1) 2 (cnt from rec_id=2)
20 5 (cnt from rec_id=1) 1 (cnt from rec_id=2)


The order should be deptno,REc_id.
Re: Logic for displaying below row as first row [message #653810 is a reply to message #653809] Tue, 19 July 2016 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Where do come CNT_1 and CNT_2?

Re: Logic for displaying below row as first row [message #653811 is a reply to message #653810] Tue, 19 July 2016 06:16 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

It needs to be derived from cnt field for each deptno.
Re: Logic for displaying below row as first row [message #653812 is a reply to message #653811] Tue, 19 July 2016 06:39 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Perhaps it would help if you started by giving us a CREATE TABLE and some INSERTs to populate that that table with your test case.
Re: Logic for displaying below row as first row [message #653814 is a reply to message #653811] Tue, 19 July 2016 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sss111ind wrote on Tue, 19 July 2016 13:16
It needs to be derived from cnt field for each deptno.


How?

Re: Logic for displaying below row as first row [message #653815 is a reply to message #653814] Tue, 19 July 2016 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The order should be deptno,REc_id.


This does not give a deterministic order of the rows.

Re: Logic for displaying below row as first row [message #653843 is a reply to message #653805] Wed, 20 July 2016 03:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.

Re: Logic for displaying below row as first row [message #653876 is a reply to message #653843] Thu, 21 July 2016 02:20 Go to previous message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Dear Madam and All

I wanted to take a moment to thank you all. I always appreciate your ability to make the difference and for that I will always be grateful.

Regards,
Nathan
Previous Topic: Collection Last Record
Next Topic: ORA-01006
Goto Forum:
  


Current Time: Thu Mar 28 16:22:01 CDT 2024