SQL Query Request [message #657463] |
Wed, 09 November 2016 07:44 |
|
raj85844
Messages: 26 Registered: November 2016 Location: chennai
|
Junior Member |
|
|
We have a table with 10 million records
and a distinct of 97 products
so as an whole we understand table is having duplicates in it
now to keep the latest record we have used a dense rank in it
which keeps the rank 1 foe every product and selects all other records starting from rank 2 for all the distinct products
now an addition condition need to be added to the dense rank as out of 97 products 1 product alone need to be retained with duplicates(as such rank 1 ....n)
How can i archive this ?
I was restricted to not remove dense rank statement as such there is a "ntile" 11g function which is written on top of it
If you can help me out this could be really appreciated. Thanks guys
|
|
|
|
|
|
Re: SQL Query Request [message #657467 is a reply to message #657466] |
Wed, 09 November 2016 08:13 |
|
raj85844
Messages: 26 Registered: November 2016 Location: chennai
|
Junior Member |
|
|
Thanks Michel for the reply
The below mentioned is an example of the above scenario
SELECT Customer, Product, NTILE (8) OVER (ORDER BY Customer DESC) AS mynitle
FROM (SELECT Customer, Product, DRANK
FROM (SELECT Customer,
Product,
DENSE_RANK ()
OVER (PARTITION BY Customer, Product
ORDER BY date DESC)
AS Drank
FROM Master_Table
WHERE subsre (product, 1, 4) = ('x001'))
WHERE Drank > 1)
*BlackSwan corrected {code} tags
[Updated on: Wed, 09 November 2016 08:21] by Moderator Report message to a moderator
|
|
|
|
|
Re: SQL Query Request [message #657470 is a reply to message #657469] |
Wed, 09 November 2016 08:22 |
|
raj85844
Messages: 26 Registered: November 2016 Location: chennai
|
Junior Member |
|
|
SELECT Customer, Product, NTILE (8) OVER (ORDER BY Customer DESC) AS mynitle
FROM (SELECT Customer, Product, DRANK
FROM (SELECT Customer,
Product,
DENSE_RANK ()
OVER (PARTITION BY Customer, Product
ORDER BY date DESC)
AS Drank[code][/code]
FROM Master_Table
WHERE subsre (product, 1, 4) = ('x001'))
WHERE Drank > 1)
|
|
|
|
|
Re: SQL Query Request [message #657474 is a reply to message #657473] |
Wed, 09 November 2016 10:56 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
As I said a simple UNION ALL will do the trick (with 10 the special department):
SQL> break on deptno skip 1
SQL> select deptno, sal, ename
2 from ( select deptno, sal, ename,
3 rank() over (partition by deptno order by sal desc) rk
4 from emp
5 where deptno != 10 )
6 where rk = 1
7 union all
8 select deptno, sal, ename
9 from emp
10 where deptno = 10
11 order by 1, 2 desc
12 /
DEPTNO SAL ENAME
---------- ---------- ----------
10 5000 KING
2450 CLARK
1300 MILLER
20 3000 SCOTT
3000 FORD
30 2850 BLAKE
Or an OR (depending on the indexes and data one or the other one will be faster):
SQL> select deptno, sal, ename
2 from ( select deptno, sal, ename,
3 rank() over (partition by deptno order by sal desc) rk
4 from emp )
5 where rk = 1 or deptno = 10
6 order by 1, 2 desc
7 /
DEPTNO SAL ENAME
---------- ---------- ----------
10 5000 KING
2450 CLARK
1300 MILLER
20 3000 SCOTT
3000 FORD
30 2850 BLAKE
|
|
|
|
|
|
Re: SQL Query Request [message #657514 is a reply to message #657513] |
Fri, 11 November 2016 10:19 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I don't understand clearly what you want and would be better if you SHOW us but if you just select the rowid in the previous query and delete rows with rowid (not) in these ones that should do the trick:
SQL> break on deptno dup skip 1
SQL> -- All rows
SQL> select deptno, sal, ename,
2 rank() over (partition by deptno order by sal desc) rk
3 from emp
4 order by 1, 2 desc
5 /
DEPTNO SAL ENAME RK
---------- ---------- ---------- ----------
10 5000 KING 1
10 2450 CLARK 2
10 1300 MILLER 3
20 3000 SCOTT 1
20 3000 FORD 1
20 2975 JONES 3
20 1100 ADAMS 4
20 800 SMITH 5
30 2850 BLAKE 1
30 1600 ALLEN 2
30 1500 TURNER 3
30 1250 MARTIN 4
30 1250 WARD 4
30 950 JAMES 6
14 rows selected.
SQL> -- Rows to delete
SQL> select deptno, sal, ename, rk
2 from ( select deptno, sal, ename,
3 rank() over (partition by deptno order by sal desc) rk
4 from emp )
5 where rk = 1 or deptno = 10
6 order by 1, 2 desc
7 /
DEPTNO SAL ENAME RK
---------- ---------- ---------- ----------
10 5000 KING 1
10 2450 CLARK 2
10 1300 MILLER 3
20 3000 SCOTT 1
20 3000 FORD 1
30 2850 BLAKE 1
6 rows selected.
SQL> -- Delete rows
SQL> delete emp
2 where rowid in (
3 select rid
4 from ( select deptno, rowid rid,
5 rank() over (partition by deptno order by sal desc) rk
6 from emp )
7 where rk = 1 or deptno = 10
8 )
9 /
6 rows deleted.
SQL> -- Remaining rows
SQL> select deptno, sal, ename,
2 rank() over (partition by deptno order by sal desc) new_rk
3 from emp
4 order by 1, 2 desc
5 /
DEPTNO SAL ENAME NEW_RK
---------- ---------- ---------- ----------
20 2975 JONES 1
20 1100 ADAMS 2
20 800 SMITH 3
30 1600 ALLEN 1
30 1500 TURNER 2
30 1250 WARD 3
30 1250 MARTIN 3
30 950 JAMES 5
8 rows selected.
|
|
|