Home » SQL & PL/SQL » SQL & PL/SQL » Need Query (Oracle 10g)
Need Query [message #423794] Mon, 28 September 2009 05:26 Go to next message
mamalik
Messages: 270
Registered: November 2008
Location: Pakistan
Senior Member
Dear All

My query is returning data like follwing.

NAME VALUE
A 1
A 1
B 2

look "A" is appearing twice in above ,value for 2nd "A" should be 0.

I need a query which return 0 values if name is duplicating.If a name is appearing more than one time ,value should be return in first row and 0 in other rows.

now result of above data should be

NAME VALUE
A 1
A 0
B 2

Name "A" can repeart more than two times.

Best Regards.
Asif.

Report message to a moderator

Re: Need Query [message #423795 is a reply to message #423794] Mon, 28 September 2009 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
110 posts and you still don't know how to post, shame on you!

More, I answered this question a couple of hours ago, search.

Regards
Michel

[Updated on: Mon, 28 September 2009 05:32]

Report message to a moderator

Re: Need Query [message #423796 is a reply to message #423794] Mon, 28 September 2009 05:34 Go to previous messageGo to next message
mamalik
Messages: 270
Registered: November 2008
Location: Pakistan
Senior Member
Dear Michle Uncle,

Michle uncle I am very sorry. I tried my best but failed to ident it. I knew that i will get anger by this posting. I am really sorry. Please help me how can i ident it?

Sorry Sir.

Best Regards.
Asif.

Report message to a moderator

Re: Need Query [message #423797 is a reply to message #423796] Mon, 28 September 2009 06:08 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
ROW_NUMBER() with CASE can solve your problem.

regards,
Delna

Report message to a moderator

Re: Need Query [message #423803 is a reply to message #423796] Mon, 28 September 2009 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I knew that i will get anger by this posting. I am really sorry.

No you are not otherwise you would post it correctly, with format, with columns aligned, with test case, with version number on 4 decimals...

Regards
Michel

[Updated on: Mon, 28 September 2009 07:17]

Report message to a moderator

Re: Need Query [message #423879 is a reply to message #423794] Mon, 28 September 2009 23:43 Go to previous messageGo to next message
mamalik
Messages: 270
Registered: November 2008
Location: Pakistan
Senior Member
Dear Michle Uncle,
Believe me, I have tried my best to indent code but failed. Please help. I also read posting guidelines and not found how i can ident columns.


Best Regards.
Asif.

Report message to a moderator

Re: Need Query [message #423882 is a reply to message #423879] Tue, 29 September 2009 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
How to format your post?
If you are providing code snippets such as SQL*Plus copy/paste, please be sure to use formatting tags:
[code] Enter your code here.[/code]

And test case: create table and insert statements along with the result you want with these data.

Is it so hard? If you fail in this, I think you are not in the right track and should think about changing your occupation in something far away from computer.

Regards
Michel

Report message to a moderator

Re: Need Query [message #423883 is a reply to message #423879] Tue, 29 September 2009 00:59 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
What did you didn't get?
That is so simple. You have to use CODE tag (last button in FORMATTING TOOLS while posting) - that is [ CODE]..[/CODE].

regards,
Delna

Report message to a moderator

Re: Need Query [message #424032 is a reply to message #423883] Wed, 30 September 2009 00:33 Go to previous messageGo to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
Can any one of you post the answer for this please

thanks in advance
sen

Report message to a moderator

Re: Need Query [message #424038 is a reply to message #424032] Wed, 30 September 2009 00:51 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
 
SQL>r
  1  select col1, col2,
  2   case col3
  3    when 1 then col2
  4    else 0
  5   end expr
  6  from
  7   (select col1, col2,
  8   row_number() over (partition by col1 order by col2) col3
  9*  from t2)

COL1        COL2       EXPR
----- ---------- ----------
a              1          1
a              1          0
a              1          0
b              1          1
b              1          0
c              1          1
c              1          0
c              1          0

8 rows selected.


regards,
Delna

Report message to a moderator

Re: Need Query [message #424041 is a reply to message #424038] Wed, 30 September 2009 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
@delna.sexy,

Do not post a solution, above all to a person that did not show any effort in his posts or tries.
You gave him clues to do it, he should be able to show us he tried to find the solution. Providing the solution does not help him for the future.

Regards
Michel

Report message to a moderator

Re: Need Query [message #424044 is a reply to message #424041] Wed, 30 September 2009 01:17 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Actually I posted the clue before answer.
Quote:
ROW_NUMBER() with CASE can solve your problem.


Then also he was asking answer, and that's why I replied with answer.

regards,
Delna

Report message to a moderator

Re: Need Query [message #424059 is a reply to message #424044] Wed, 30 September 2009 02:38 Go to previous message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
delna.sexy wrote on Wed, 30 September 2009 08:17
Actually I posted the clue before answer.
Quote:
ROW_NUMBER() with CASE can solve your problem.


Then also he was asking answer, and that's why I replied with answer.

regards,
Delna

Yes I know you posted a clue (read my post I mentioned it).
Yes, of course, he asks for a solution, he asked it from the first post, this is not a reason to post it unless he proved he made at least a small effort to find it.

Regards
Michel

Report message to a moderator

Previous Topic: not to allow Special character thru insert statement
Next Topic: drill down web report in oracle 9i using hyperlink
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Jul 07 18:21:35 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.