Home » SQL & PL/SQL » SQL & PL/SQL » Limiting Number of Duplicates?
Limiting Number of Duplicates? [message #295383] Tue, 22 January 2008 04:13 Go to next message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member
Gurus -

Is there any way in limiting number of duplicates while selecting records?

For Ex,

If i have 10 duplicates, i have to select only 4 and i have to ignore all other duplicates.

Thanks,
Sam.
Re: Limiting Number of Duplicates? [message #295392 is a reply to message #295383] Tue, 22 January 2008 04:30 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
You could use the RANK function to do this (as 1 option)
Re: Limiting Number of Duplicates? [message #295396 is a reply to message #295383] Tue, 22 January 2008 04:33 Go to previous messageGo to next message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member
Thanks Pablolee!!!

Can you please give me an example?

Thanks,
Sam
Re: Limiting Number of Duplicates? [message #295405 is a reply to message #295396] Tue, 22 January 2008 04:59 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
here
Have a read, have a try, come back with any difficulties. When/if you come back with more questions, please post a test case, i.e. create table and insert scripts that will create a reproduceable scenario for your requirements.
Also have a read on the forum posting guidelines for help with formatting etc.
Re: Limiting Number of Duplicates? [message #295406 is a reply to message #295396] Tue, 22 January 2008 05:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
RANK
DENSE_RANK
ROW_NUMBER

Regards
Michel
Re: Limiting Number of Duplicates? [message #295413 is a reply to message #295383] Tue, 22 January 2008 05:11 Go to previous messageGo to next message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member
Thanks!!

I have tried row_number function as follows,


SQL> SELECT NAME FROM TEST
  2  /

NAME
--------
RECORD1
RECORD1
RECORD1
RECORD1
RECORD2
RECORD3
RECORD1
RECORD2

  1  SELECT NAME,ROW_NUMBER () OVER (PARTITION BY NAME ORDER BY NAME)
  2  FROM DD
SQL> /

NAME     ROW_NUMBER()OVER(PARTITIONBYNAMEORDERBYNAME)
-------- --------------------------------------------
RECORD1                                             1
RECORD1                                             2
RECORD1                                             3
RECORD1                                             4
RECORD1                                             5
RECORD2                                             1
RECORD2                                             2
RECORD3                                             1



But, I am not able to select 4 duplicates values from this.

So that, my output looks as below,

NAME
--------
RECORD1
RECORD1
RECORD1
RECORD1
RECORD2         
RECORD2         
RECORD3      



Can you help me?

Thanks
Sam
Re: Limiting Number of Duplicates? [message #295416 is a reply to message #295413] Tue, 22 January 2008 05:19 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
What you now want to do, is place the query that you have got, into a subquery, alias the row_number expression
and in the outer query restrict that column:
SELECT col1
FROM (SELECT col1, row_number.... rn
      FROM yourtable)
WHERE rn <= yourlimit;
Re: Limiting Number of Duplicates? [message #295422 is a reply to message #295383] Tue, 22 January 2008 05:23 Go to previous messageGo to next message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member
Cool!!!

Thanks Pablolee..,

That worked perfectly!!!

Thanks,
Sam
Re: Limiting Number of Duplicates? [message #295426 is a reply to message #295422] Tue, 22 January 2008 05:30 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
You wrote the code, I just pointed you where to go.
Thumbs Up
Re: Limiting Number of Duplicates? [message #295428 is a reply to message #295383] Tue, 22 January 2008 05:32 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
here is the complete query

SQL> select * from emp;

     EMPID EMPNA      SUPID DEPT        MGR        SAL
---------- ----- ---------- ---- ---------- ----------
       100 anu            1 10          301       3900
         1 sid           50 20          301       1000
        50 sagar        100 50          305       1000
         2 Rahul       1000 10          302       3000
         3 Rai          200 10          302       2500
         4 Raj          300 20          304       1000
         5 Ram          300 10          305       9000
         7 rao          200 10          301       8700
         6 CRA          400 10          306       1000
         7 Jamm         200 10          301       8700
       100 anu            1 10          301       3900
       100 anu            1 10          301       3900
       100 anu            1 10          301       3900
       100 anu            1 10          301       3900
       100 anu            1 10          301       3900
       100 anu            1 10          301       3900
       100 anu            1 10          301       3900
         1 sid           50 20          301       1000
         1 sid           50 20          301       1000
         1 sid           50 20          301       1000
         1 sid           50 20          301       1000

21 rows selected.

SQL>  select empid,empname,sal,mgr from (select empid,empname,mgr,sal,
row_number() over(partition by empid order by empid)rn from emp)
     where rn < 3 ;

     EMPID EMPNA        SAL        MGR
---------- ----- ---------- ----------
         1 sid         1000        301
         1 sid         1000        301
         2 Rahul       3000        302
         3 Rai         2500        302
         4 Raj         1000        304
         5 Ram         9000        305
         6 CRA         1000        306
         7 rao         8700        301
         7 jam         8700        301
        50 sagar       1000        305
       100 anu         3900        301
       100 anu         3900        301
12 rows selected.

[Updated on: Tue, 22 January 2008 05:32] by Moderator

Report message to a moderator

Re: Limiting Number of Duplicates? [message #295430 is a reply to message #295428] Tue, 22 January 2008 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ What does this add to pablolee's answer?
2/ Keep your lines within 80 characters.

Regards
Michel
Re: Limiting Number of Duplicates? [message #295437 is a reply to message #295383] Tue, 22 January 2008 05:49 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
@Michel,

I was just thinking on this query and as soon as i found the solution , i just posted it without refering/checking whether some one else already posted the answer.
So, next time will check it before posting.

[Updated on: Tue, 22 January 2008 06:02] by Moderator

Report message to a moderator

Re: Limiting Number of Duplicates? [message #295444 is a reply to message #295437] Tue, 22 January 2008 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And next time, don't use code tags without checking your line size.
Use the "Preview" button.

Regards
Michel
Re: Limiting Number of Duplicates? [message #303485 is a reply to message #295383] Fri, 29 February 2008 09:10 Go to previous message
rjsha1
Messages: 22
Registered: December 2005
Location: Birmingham England
Junior Member
thanks xample has come in very useful
Previous Topic: difference between trunc and delete
Next Topic: Display ratio of 2 numbers
Goto Forum:
  


Current Time: Thu Dec 08 16:42:27 CST 2016

Total time taken to generate the page: 0.07114 seconds