Data grouping [message #655847] |
Wed, 14 September 2016 06:46 |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
Hi,
Please provide built-in function getting group data with single value wihtout any duplicates in column value something like below.
Here for example, the column Invoice number should have one value for many values in other column (Grouping data).
Invoice number Invoice user days_process
1 ABC 2001
XYZ 2002
PQR 2003
2 OUT 9032
YER 9033
3 LJK 9044
TER 3033
TFT 3045
Please advise.Your help is highly appreciated.
Thank you.
Regards,
SRK
[EDITED by LF: applied [code] tags]
[Updated on: Wed, 14 September 2016 07:15] by Moderator Report message to a moderator
|
|
|
|
Re: Data grouping [message #655849 is a reply to message #655848] |
Wed, 14 September 2016 07:23 |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
Thank you Littlefoot.
But is there something code/function we can include along with the query? Because we my not be calling this in SQL plus!!
Thanks again.
Regards,
SRK
|
|
|
|
Re: Data grouping [message #655853 is a reply to message #655849] |
Wed, 14 September 2016 08:27 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
sure
select decode (lag(invoice_number) over (order by invoice_number,days_process),
invoice_number,null,invoice_number) as invoice_number,invoice_user,days_process
from my_table
order by invoice_number,days_process;
|
|
|
|
Re: Data grouping [message #655855 is a reply to message #655854] |
Wed, 14 September 2016 08:40 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> select nullif(deptno,lag(deptno) over(order by deptno, ename, empno)) deptno,
2 ename, job, sal
3 from emp
4 order by emp.deptno, ename, empno
5 /
DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
10 CLARK MANAGER 2450
KING PRESIDENT 5000
MILLER CLERK 1300
20 ADAMS CLERK 1100
FORD ANALYST 3000
JONES MANAGER 2975
SCOTT ANALYST 3000
SMITH CLERK 800
30 ALLEN SALESMAN 1600
BLAKE MANAGER 2850
JAMES CLERK 950
MARTIN SALESMAN 1250
TURNER SALESMAN 1500
WARD SALESMAN 1250
SQL> select decode(rank() over(partition by deptno order by ename, empno), 1,deptno) deptno,
2 ename, job, sal
3 from emp
4 order by emp.deptno, ename, empno
5 /
DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
10 CLARK MANAGER 2450
KING PRESIDENT 5000
MILLER CLERK 1300
20 ADAMS CLERK 1100
FORD ANALYST 3000
JONES MANAGER 2975
SCOTT ANALYST 3000
SMITH CLERK 800
30 ALLEN SALESMAN 1600
BLAKE MANAGER 2850
JAMES CLERK 950
MARTIN SALESMAN 1250
TURNER SALESMAN 1500
WARD SALESMAN 1250
|
|
|
|