OraFAQ Forum: SQL & PL/SQL » SQL Query Request

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Request (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: SQL Query Request [message #657464 is a reply to message #657463]

Wed, 09 November 2016 07:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: SQL Query Request [message #657465 is a reply to message #657464]

Wed, 09 November 2016 07:53

raj85844
Messages: 26
Registered: November 2016
Location: chennai

Junior Member

Thanks for approving the Request.

Report message to a moderator

Re: SQL Query Request [message #657466 is a reply to message #657463]

Wed, 09 November 2016 07:54

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Don't understand this: "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)"

You mean that, for instance, product 42 will have all his "duplicates" while the other will have only the "lastest" one? A simple UNION ALL will do the trick.

First you have to define "duplicate".
Then, you should post a test case: create table and insert statements for an example showing what you want.
Before, read the links BlackSwan gave you.

Report message to a moderator

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 #657468 is a reply to message #657467]

Wed, 09 November 2016 08:18

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read and pay attention to the link we gave you and to our post.

Report message to a moderator

Re: SQL Query Request [message #657469 is a reply to message #657467]

Wed, 09 November 2016 08:19

raj85844
Messages: 26
Registered: November 2016
Location: chennai

Junior Member

Products
------------
x001-13434
x001-13434
a003-53443
a003-53443
b931-94793
b931-94793

the mention above are a samples of the product
And my request is to keep one product that has a pattern starting with first 4 letter as 'x001' must be retained in the table even it has duplicates

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)

Report message to a moderator

Re: SQL Query Request [message #657471 is a reply to message #657470]

Wed, 09 November 2016 10:02

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Wed, 09 November 2016 14:54

Don't understand this: "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)"

You mean that, for instance, product 42 will have all his "duplicates" while the other will have only the "lastest" one? A simple UNION ALL will do the trick.

First you have to define "duplicate".
Then, you should post a test case: create table and insert statements for an example showing what you want.
Before, read the links BlackSwan gave you.

Report message to a moderator

Re: SQL Query Request [message #657473 is a reply to message #657471]

Wed, 09 November 2016 10:38

raj85844
Messages: 26
Registered: November 2016
Location: chennai

Junior Member

Hi Michel,

Yes u understood it right..

In simple terms consider employee and departments table here I want the employee of each department having highest salary as mentioning it as rank 1.

The above query can be formed in many ways in our scenario we are using dense rank to achieve it.

Now the addition condition that I want to add to the above query is "say for example department 90 alone should display all range of salary and remaining department should display only highest salary that is rank 1

Hope you could help me out in this piece of query thank you for you knowledge and forgive my mistakes in posting things not accurate I will correct my mistakes as progress .

Report message to a moderator

Re: SQL Query Request [message #657474 is a reply to message #657473]

Wed, 09 November 2016 10:56

Michel Cadot
Messages: 68643
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

Report message to a moderator

Re: SQL Query Request [message #657475 is a reply to message #657474]

Wed, 09 November 2016 11:05

raj85844
Messages: 26
Registered: November 2016
Location: chennai

Junior Member

Thanks Michel, Yes agree this should meet my requirement in a case of 10 million records table this union all or or will it be a right option

Report message to a moderator

Re: SQL Query Request [message #657476 is a reply to message #657475]

Wed, 09 November 2016 12:02

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I don't know, it depends on other requirements like response time and your ability to create indexes (if some are missing) or other things like that. You have to test.

Report message to a moderator

Re: SQL Query Request [message #657513 is a reply to message #657476]

Fri, 11 November 2016 06:44

raj85844
Messages: 26
Registered: November 2016
Location: chennai

Junior Member

Hi Michel,

Provided information was much appreciated,
As needed little more help ... Hope u could crack it ..

In spite of "selecting" the data here I am trying "deleting"

requirement stated below
-------------------------

1. In department_id column having data s with a pattern like 90_001 , 90_002 , 90_003 similarly for rest of all departments with this format with "duplicates"
2. Here we are going to retain the department starting with the pattern "90_" ( department_id not like ('90_%') )
3. To achieve this I am using dense_rank but not able to get the pattern that are hidden in rank 2 and rank 3 and so on ....
4. similarly to eliminate the rank 2 and rank 3 and so on from other departments.

Hope u could help me in this... Waiting for the Best Michel. Thanks and Cheers.

Report message to a moderator

Re: SQL Query Request [message #657514 is a reply to message #657513]

Fri, 11 November 2016 10:19

Michel Cadot
Messages: 68643
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.

Report message to a moderator

Previous Topic:	spool sql data in excel multi sheets
Next Topic:	PL/SQL Email

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 11:33:11 CDT 2024