Query2Partition2DistinctRecords

View All Public Scripts Login to Run Script

Script Name Query2Partition2DistinctRecords
Visibility Unlisted - anyone with the share link can access
Description This query is useful to get distinct records from the partitioned data set.Thanks
Area SQL Analytics
Contributor vadivelraja@gmail.com
Created Monday July 01, 2019

Statement 1

select sum(lvl) over (partition by md_lvl2)  
from  
(  
select level lvl,mod(level,2) md_lvl2,mod(level,3) md_lvl3  
from dual  
connect by level < 100)


SUM(LVL)OVER(PARTITIONBYMD_LVL2)
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2500

Rows 1 - 50. More rows exist.

SUM(LVL)OVER(PARTITIONBYMD_LVL2)
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2450
2500

Statement 2

select md_lvl2 
,sum(lvl) over (partition by md_lvl2)  
from  
(  
select level lvl,mod(level,2) md_lvl2,mod(level,3) md_lvl3  
from dual  
connect by level < 100)


MD_LVL2 SUM(LVL)OVER(PARTITIONBYMD_LVL2)
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
1 2500

Rows 1 - 50. More rows exist.

MD_LVL2	SUM(LVL)OVER(PARTITIONBYMD_LVL2)
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
1	2500

Statement 3

select md_lvl2 
,sum(lvl) over (partition by md_lvl2 order by md_lvl3 desc)  
from  
(  
select level lvl,mod(level,2) md_lvl2,mod(level,3) md_lvl3  
from dual  
connect by level < 100)


MD_LVL2 SUM(LVL)OVER(PARTITIONBYMD_LVL2ORDERBYMD_LVL3DESC)
0 850
0 850
0 850
0 850
0 850
0 850
0 850
0 850
0 850
0 850
0 850
0 850
0 850
0 850
0 850
0 850
0 850
0 1634
0 1634
0 1634
0 1634
0 1634
0 1634
0 1634
0 1634
0 1634
0 1634
0 1634
0 1634
0 1634
0 1634
0 1634
0 1634
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
0 2450
1 800

Rows 1 - 50. More rows exist.

MD_LVL2	SUM(LVL)OVER(PARTITIONBYMD_LVL2ORDERBYMD_LVL3DESC)
0	850
0	850
0	850
0	850
0	850
0	850
0	850
0	850
0	850
0	850
0	850
0	850
0	850
0	850
0	850
0	850
0	850
0	1634
0	1634
0	1634
0	1634
0	1634
0	1634
0	1634
0	1634
0	1634
0	1634
0	1634
0	1634
0	1634
0	1634
0	1634
0	1634
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
0	2450
1	800

Statement 4

select md_lvl2 
,md_lvl3 
,sum(lvl) over (partition by md_lvl2 order by md_lvl3 desc)  
from  
(  
select level lvl,mod(level,2) md_lvl2,mod(level,3) md_lvl3  
from dual  
connect by level < 100)


MD_LVL2 MD_LVL3 SUM(LVL)OVER(PARTITIONBYMD_LVL2ORDERBYMD_LVL3DESC)
0 2 850
0 2 850
0 2 850
0 2 850
0 2 850
0 2 850
0 2 850
0 2 850
0 2 850
0 2 850
0 2 850
0 2 850
0 2 850
0 2 850
0 2 850
0 2 850
0 2 850
0 1 1634
0 1 1634
0 1 1634
0 1 1634
0 1 1634
0 1 1634
0 1 1634
0 1 1634
0 1 1634
0 1 1634
0 1 1634
0 1 1634
0 1 1634
0 1 1634
0 1 1634
0 1 1634
0 0 2450
0 0 2450
0 0 2450
0 0 2450
0 0 2450
0 0 2450
0 0 2450
0 0 2450
0 0 2450
0 0 2450
0 0 2450
0 0 2450
0 0 2450
0 0 2450
0 0 2450
0 0 2450
1 2 800

Rows 1 - 50. More rows exist.

MD_LVL2	MD_LVL3	SUM(LVL)OVER(PARTITIONBYMD_LVL2ORDERBYMD_LVL3DESC)
0	2	850
0	2	850
0	2	850
0	2	850
0	2	850
0	2	850
0	2	850
0	2	850
0	2	850
0	2	850
0	2	850
0	2	850
0	2	850
0	2	850
0	2	850
0	2	850
0	2	850
0	1	1634
0	1	1634
0	1	1634
0	1	1634
0	1	1634
0	1	1634
0	1	1634
0	1	1634
0	1	1634
0	1	1634
0	1	1634
0	1	1634
0	1	1634
0	1	1634
0	1	1634
0	1	1634
0	0	2450
0	0	2450
0	0	2450
0	0	2450
0	0	2450
0	0	2450
0	0	2450
0	0	2450
0	0	2450
0	0	2450
0	0	2450
0	0	2450
0	0	2450
0	0	2450
0	0	2450
0	0	2450
1	2	800

Statement 5

select distinct(md_lvl2||','||md_lvl3||','||sumrec) 
from 
( 
select md_lvl2 
,md_lvl3 
,sum(lvl) over (partition by md_lvl2 order by md_lvl3 desc) sumrec 
from  
(  
select level lvl,mod(level,2) md_lvl2,mod(level,3) md_lvl3  
from dual  
connect by level < 100)) 
order by 1


(MD_LVL2||','||MD_LVL3||','||SUMREC)
0,0,2450
0,1,1634
0,2,850
1,0,2500
1,1,1633
1,2,800

6 rows selected.

(MD_LVL2\|\|','\|\|MD_LVL3\|\|','\|\|SUMREC)
0,0,2450
0,1,1634
0,2,850
1,0,2500
1,1,1633
1,2,800

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database