Home » SQL & PL/SQL » SQL & PL/SQL » oracle 9i (two rows with null)
oracle 9i [message #432080] Fri, 20 November 2009 23:47 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Hi experts goo morning

I want to retrieve first two rows along with two null values for each group.
the data as follows

deptno sal
10 2000
10 3000
10
20 4000
20 5000
20
20
30 1000
30 3000
30 2000
30 5000
30
30

In this scenario the output should like this

20 4000
20 5000
20
20
30 1000
30 3000
30 2000
30 5000
30
30

please help me thanks in advance
Re: oracle 9i [message #432081 is a reply to message #432080] Fri, 20 November 2009 23:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
1. We don't do home work
or
2. What is business application that demands this requirement?
Re: oracle 9i [message #432082 is a reply to message #432081] Sat, 21 November 2009 00:02 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
As Black swan said,
We don`t do home work for you...
A simple sql will give you the result...
And copy and paste your sql session of what you tried and what you got... Smile

And before posting read the Orafaq guide lines how to format your post.


sriram

Re: oracle 9i [message #432090 is a reply to message #432082] Sat, 21 November 2009 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...And post a working Test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: oracle 9i [message #432153 is a reply to message #432082] Sun, 22 November 2009 04:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ramoradba wrote on Sat, 21 November 2009 07:02
As Black swan said,
We don`t do home work for you...
A simple sql will give you the result...
And copy and paste your sql session of what you tried and what you got... Smile

And before posting read the Orafaq guide lines how to format your post.


sriram


Please stop this back-seat moderating. The post is perfectly readable, so no use to refer the poster to the posting guidelines.
Re: oracle 9i [message #432264 is a reply to message #432080] Mon, 23 November 2009 05:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Based on the results that you posted,I assume that the question that you meant to ask was' How can I show all the rows from each group that contains at least two null values'

I would base my query on something like this:
SELECT group_id
FROM   table
having sum(nvl2(column,0,1))>2
Re: oracle 9i [message #432437 is a reply to message #432264] Tue, 24 November 2009 04:19 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Sir, I think it should be '>='
SELECT group_id
FROM   table
group by group_id
having sum(nvl2(column,0,1))>= 2


Regards,
Ved
Previous Topic: how to get outer table value in the nested subquery
Next Topic: un-known table in schema
Goto Forum:
  


Current Time: Wed Dec 07 23:55:43 CST 2016

Total time taken to generate the page: 0.11038 seconds