OraFAQ Forum: SQL & PL/SQL » How to get the result for my query

Home » SQL & PL/SQL » SQL & PL/SQL » How to get the result for my query

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

How to get the result for my query [message #627039]

Thu, 06 November 2014 13:23

venki459
Messages: 5
Registered: November 2014
Location: CHENNAI

Junior Member

select * from aris_g

Name_dug NO
A 20
A 21
A 50
B 30
B 15
B 45
B 46
Result Output Should Be

Name_dug NO
A 20
21
50
B 30
15
45
46
Name_dug should repeate only once for 'A' and 'B'

Report message to a moderator

Re: How to get the result for my query [message #627040 is a reply to message #627039]

Thu, 06 November 2014 13:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/mv/msg/195074/626771/#msg_626771

Report message to a moderator

Re: How to get the result for my query [message #627041 is a reply to message #627039]

Thu, 06 November 2014 14:08

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

Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) 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.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: How to get the result for my query [message #627043 is a reply to message #627039]

Thu, 06 November 2014 14:11

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

Senior Member
Account Moderator

Note that this is a FAQ which depends on your client program. Is this SQL*Plus?
What is your Oracle version?

These are information you should give in each of your question and this is explain in the links we gave you.

[Updated on: Thu, 06 November 2014 14:12]

Report message to a moderator

Re: How to get the result for my query [message #627045 is a reply to message #627039]

Thu, 06 November 2014 14:30

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

IF (and I repeat and emphasize "IF") you are using sqlplus, look at 'break on name_dug'.

What you appear to be after is a question for whatever client program you are using, not SQL.

Report message to a moderator

Re: How to get the result for my query [message #627056 is a reply to message #627039]

Thu, 06 November 2014 20:55

venki459
Messages: 5
Registered: November 2014
Location: CHENNAI

Junior Member

venki459 wrote on Thu, 06 November 2014 13:23

select * from aris_g

Name_dug NO
A 20
A 21
A 50
B 30
B 15
B 45
B 46
Result Output Should Be

Name_dug NO
A 20
21
50
B 30
15
45
46
Name_dug should repeate only once for 'A' and 'B'

we are using oracle10g and oracle 11g

Report message to a moderator

Re: How to get the result for my query [message #627064 is a reply to message #627056]

Fri, 07 November 2014 00:48

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

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 06 November 2014 21:08

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) 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.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: How to get the result for my query [message #627089 is a reply to message #627039]

Fri, 07 November 2014 03:13

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

As asked by the forum, you should always provide the create table and data script along with a test case and the work that you have tried. It becomes easy to help in that case.

Based on my assumption, you can use analytical function

SQL> SELECT DECODE(row1,1,Name_dug1,NULL) Name_dug,NO FROM
  2  (
  3  WITH DATA1 AS
  4  (SELECT 'A' Name_dug , 20 NO FROM dual UNION ALL
  5  SELECT 'A' Name_dug , 21 NO FROM dual UNION ALL
  6  SELECT 'A' Name_dug , 50 NO FROM dual UNION ALL
  7  SELECT 'B' Name_dug , 30 NO FROM dual UNION ALL
  8  SELECT 'B' Name_dug , 15 NO FROM dual UNION ALL
  9  SELECT 'B' Name_dug , 46 NO FROM dual UNION ALL
 10  SELECT 'B' Name_dug , 45 NO FROM dual
 11  )
 12  SELECT Name_dug Name_dug1,NO, ROW_NUMBER() OVER (PARTITION BY name_dug ORDER BY NO) row1 FROM data1
 13  ) ORDER BY Name_dug1;
NAME_DUG         NO
-------- ----------
A                20
                 21
                 50
B                15
                 30
                 45
                 46
7 rows selected

[Updated on: Fri, 07 November 2014 03:16]

Report message to a moderator

Re: How to get the result for my query [message #627091 is a reply to message #627089]

Fri, 07 November 2014 03:17

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

Senior Member
Account Moderator

And as asked by the forum, you should NOT give a complete solution for basic queries or FAQ and let people learn by themselves.

Note that if OP's client program is SQL*Plus your SQL query is useless, better let SQL*Plus do the work.
And if it is better to do it in the client program (as Ed pointed to) and NOT to do it in SQL.

Report message to a moderator

Re: How to get the result for my query [message #627119 is a reply to message #627056]

Fri, 07 November 2014 06:35

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

[quote title=venki459 wrote on Thu, 06 November 2014 20:55]venki459 wrote on Thu, 06 November 2014 13:23

we are using oracle10g and oracle 11g

Fine, but we have also been asking/making reference to the fact that the solution has to do with whatever client program you are using. "oracle10g" and "oracle 11g" are just generic names for the database itself. They say nothing about whatever client program you are using.

Report message to a moderator

Previous Topic:	Table Creation using WITH Clause
Next Topic:	SQL Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 20:07:49 CDT 2024