Home » SQL & PL/SQL » SQL & PL/SQL » Get a specific record in duplicates (Oracle 11g)
Get a specific record in duplicates [message #563785] Fri, 17 August 2012 02:08 Go to next message
Gurusank
Messages: 8
Registered: September 2011
Junior Member
Hi Experts,

I have a requirement with source table has duplicates so i need to select a row based upon the below condition.

Source table

EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
1 JOHN 2000 A 20
1 SAM 3000 A 30

2 TOM 500 1 40
2 SUNG 1500 1 50

Desired Output

EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
2 SUNG 1500 1 50

I tried with MAX and DENSE_RANK but its not getting me the first row.

Please help.

Thanks,
Gurusank

Report message to a moderator

Re: Get a specific record in duplicates [message #563788 is a reply to message #563785] Fri, 17 August 2012 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i need to select a row based upon the below condition


There is no below condition. Post it.

From your previous topic:

Michel Cadot wrote on Fri, 16 December 2011 13:14
Same answer than to your previous MAX DATE using selected colums topic.

I don't see why you repost it... or maybe this is because there are 1 million rows more?

Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...


Please comply the rules.

Regards
Michel

Report message to a moderator

Re: Get a specific record in duplicates [message #563790 is a reply to message #563788] Fri, 17 August 2012 02:35 Go to previous messageGo to next message
Gurusank
Messages: 8
Registered: September 2011
Junior Member
Thanks Michel for responding. Basically i need to select the record with CODE as 1 among the duplicates. Please let me know if i am not clear.

Report message to a moderator

Re: Get a specific record in duplicates [message #563793 is a reply to message #563790] Fri, 17 August 2012 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ I don't know what is a duplicate as you didn't define it.
2/ "where code = 1" shoud return you the row with code 1
3/ Post a test case
4/ Explain with details the rules for the result you want

Regards
Michel

Report message to a moderator

Re: Get a specific record in duplicates [message #563810 is a reply to message #563785] Fri, 17 August 2012 05:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1397
Registered: January 2010
Senior Member
 
with sample_table as (
                      select 1 emp_no,'TOM' ename,1000 salary,'1' code,10 emp_id from dual union all
                      select 1,'JOHN',2000,'A',20 from dual union all
                      select 1,'SAM',3000,'A',30 from dual union all
                      select 2,'TOM',500,'1',40 from dual union all
                      select 2,'SUNG',1500,'1',50 from dual
                     )
select  emp_no,
        ename,
        salary,
        code,
        emp_id
  from  (
         select  s.*,
                 row_number() over(order by emp_id) rn_first,
                 row_number() over(order by emp_id desc) rn_last
           from  sample_table s
        )
  where rn_first = 1
     or rn_last = 1
  order by emp_id
/

    EMP_NO ENAM     SALARY C     EMP_ID
---------- ---- ---------- - ----------
         1 TOM        1000 1         10
         2 SUNG       1500 1         50

SQL>


SY.

Report message to a moderator

Re: Get a specific record in duplicates [message #563852 is a reply to message #563810] Fri, 17 August 2012 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Spoonfeeding is not helping.

In addition, from OP said:
2 TOM 500 1 40
is a valid answer.

Regards
Michel

[Updated on: Fri, 17 August 2012 09:58]

Report message to a moderator

Re: Get a specific record in duplicates [message #563865 is a reply to message #563852] Fri, 17 August 2012 10:51 Go to previous messageGo to next message
Gurusank
Messages: 8
Registered: September 2011
Junior Member
Michel,

Instead of bluffing and asking pointless questions why dont u really "try" to help those who need help. If not please dont intrude from those who is giving help.

Moreover, i am not here just to get spoonfeeded rather to get some ideas from the problems i encounter. For my every post u are the first to give me reply on mistakes rather than any pea nut of help.

BTW, Glad i solved the problem by myself without of your spoonfeeding.

Thanks,
Gurusank

Report message to a moderator

Re: Get a specific record in duplicates [message #563866 is a reply to message #563865] Fri, 17 August 2012 10:55 Go to previous messageGo to next message
BlackSwan
Messages: 20085
Registered: January 2009
Senior Member
Ignoring the Posting Guidelines is unprofessional.

Report message to a moderator

Re: Get a specific record in duplicates [message #563868 is a reply to message #563865] Fri, 17 August 2012 11:17 Go to previous message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Gurusank wrote on Fri, 17 August 2012 17:51
Michel,

Instead of bluffing and asking pointless questions why dont u really "try" to help those who need help. If not please dont intrude from those who is giving help.

Moreover, i am not here just to get spoonfeeded rather to get some ideas from the problems i encounter. For my every post u are the first to give me reply on mistakes rather than any pea nut of help.

BTW, Glad i solved the problem by myself without of your spoonfeeding.

Thanks,
Gurusank


You did not define what is a duplicate.
I have another answer to your question:
    EMP_NO ENAM     SALARY C     EMP_ID
---------- ---- ---------- - ----------
         1 TOM        1000 1         10
         2 SUNG       1500 1         50


Tell us why
2 TOM 500 1 40
is not in the result?
It has CODE=1 as you asked.
Is it a pointless question?

Trying to help is not providing a query that gives the same result that you ask.
I think it is much better helping to help people learn how to specify their problem and question. But of course, this is for smart and not lazy people, it seems you are not so I don't care to not help you.

At least, next time, follow the guide, if you are able to do it.

Regards
Michel

Report message to a moderator

Previous Topic: use defined exception
Next Topic: split result set into column (merged 6)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat May 18 19:08:43 CDT 2013

Total time taken to generate the page: 0.10748 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.