Home » SQL & PL/SQL » SQL & PL/SQL » Need a output from multilple rows return only one row in oracle? (Oracle 10g)
Need a output from multilple rows return only one row in oracle? [message #580740] Wed, 27 March 2013 12:37 Go to next message
victoryhendry
Messages: 85
Registered: January 2007
Location: Bangalore
Member

EMP table

create table emp1 (empno number,deptname varchar2(30),deptno number,sal number);

insert into emp1 values (1,'Bank',10,1000);
insert into emp1 values (1,'Finance',20,400);
insert into emp1 values (2,'Finance',20,4000);
insert into emp1 values (3,'Account',30,3000);

commit;

select * from emp1;

actual output :

empno deptname deptno sal
1 Bank 10 1000
1 Finane 20 400
2 Finane 20 4000
3 Account 30 3000

Expected output :

empno deptname deptno sal
1 Bank 10 1000
2 Finane 20 4000
3 Account 30 3000

I am looking a output like above one. if any empno belongs to deptname Bank then give priority to that values else go to other dept like Finance but empno 1 should return only one row.i.e Bank dept only.

I have to return only one row based on dept values for one empno

please suggest me how to do in sql or plsql?
Re: Need a output from multilple rows return only one row in oracle? [message #580744 is a reply to message #580740] Wed, 27 March 2013 12:53 Go to previous messageGo to next message
victoryhendry
Messages: 85
Registered: January 2007
Location: Bangalore
Member

could you please help me on the above one?

Regards,

Victoryhendry
Re: Need a output from multilple rows return only one row in oracle? [message #580746 is a reply to message #580744] Wed, 27 March 2013 13:07 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
And what will be the criterion if there is no Bank? For example an employee has been assigned two depts 'Account' and 'Finance', which one you take?

The rule is not clear.


Regards,
Dariyoosh

[Updated on: Wed, 27 March 2013 13:07]

Report message to a moderator

Re: Need a output from multilple rows return only one row in oracle? [message #580747 is a reply to message #580744] Wed, 27 March 2013 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And if 2 is member of Finane and Account what happens?

Regards
Michel
Re: Need a output from multilple rows return only one row in oracle? [message #580748 is a reply to message #580747] Wed, 27 March 2013 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A hint:
SQL> with
  2    data as (
  3      select empno, deptname, deptno, sal,
  4             row_number() over 
  5               (partition by empno order by decode(deptname,'Bank',0,1),deptname) 
  6               rn
  7      from emp1
  8    )
  9  select empno, deptname, deptno, sal 
 10  from data
 11  where rn = 1
 12  /
     EMPNO DEPTNAME                           DEPTNO        SAL
---------- ------------------------------ ---------- ----------
         1 Bank                                   10       1000
         2 Finance                                20       4000
         3 Account                                30       3000


Note that your model is wrong.
Read Normalization.

Regards
Michel
Re: Need a output from multilple rows return only one row in oracle? [message #580755 is a reply to message #580748] Wed, 27 March 2013 14:11 Go to previous messageGo to next message
victoryhendry
Messages: 85
Registered: January 2007
Location: Bangalore
Member

Thanks for reply.

create table emp1 (empno number,deptname varchar2(30),deptno number,sal number);

insert into emp1 values (1,'Bank',10,1000);
insert into emp1 values (1,'Finance',20,400);
insert into emp1 values (2,'Finance',20,4000);
insert into emp1 values (2,'Finance',20,8000);
insert into emp1 values (3,'Bank',10,200);
insert into emp1 values (3,'Account',30,3000);

insert into emp1 values (4,'Account',30,310);
insert into emp1 values (4,'Finace',20,990);
insert into emp1 values (4,'Account',30,9000);

insert into emp1 values (5,'IT',50,990);
insert into emp1 values (5,'Telecom',60,9000);
commit;

select * from emp1;

my priority will be given below. if any priority base dept is not available then only we have to go other dept(priority)

priority : 1 : Bank
priority : 2 : Finance
priority : 3 : Account
priority : 4 : IT
priority : 5 : Telecom
else max(sal) of dept

if any dept has multiple records for same dept then highest values for the same dept we have to select?
(i.e 2,Finance,20,8000)
insert into emp1 values (2,'Finance',20,4000);
insert into emp1 values (2,'Finance',20,8000);--we have select who has max sal for the same dept if we have multiple record for same dept.

Regards,
Victoryhendry
Re: Need a output from multilple rows return only one row in oracle? [message #580757 is a reply to message #580755] Wed, 27 March 2013 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just adapt my query changing the order as you want and post it.

From your previous topic:

Michel Cadot wrote on Tue, 21 August 2012 09:56
From your previous topics:

cookiemonster wrote on Mon, 12 March 2012 11:33
@victoryhendry - Will you please read and follow How to use [code] tags and make your code easier to read? You've been asked several times before.
...


Quote:
Michel Cadot wrote on Tue, 06 March 2012 11:50
Michel Cadot wrote on Mon, 20 February 2012 10:51
Michel Cadot wrote on Mon, 20 February 2012 10:24
With all SQL questions, 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.

Before, 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.
...

...

...



Quote:
Michel Cadot wrote on Wed, 22 February 2012 09:41
@ victoryhendry,

and it is NOT formatted.

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.

Regards
Michel

...


Regards
Michel

[Updated on: Wed, 27 March 2013 14:18]

Report message to a moderator

Re: Need a output from multilple rows return only one row in oracle? [message #580760 is a reply to message #580755] Wed, 27 March 2013 14:25 Go to previous messageGo to next message
victoryhendry
Messages: 85
Registered: January 2007
Location: Bangalore
Member

my expected output is below :


empno deptname dpetno sal
1 Bank 10 1000
2 Finance 20 4000
3 Bank 10 200
4 Finace 20 990
5 IT 50 990

Regards,

Victoryhendry
Re: Need a output from multilple rows return only one row in oracle? [message #580761 is a reply to message #580760] Wed, 27 March 2013 14:46 Go to previous messageGo to next message
victoryhendry
Messages: 85
Registered: January 2007
Location: Bangalore
Member

Please help me to get expected output as mentione like above one?


Regards,

Victoryhendry
Re: Need a output from multilple rows return only one row in oracle? [message #580762 is a reply to message #580761] Wed, 27 March 2013 14:59 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 27 March 2013 20:15
Just adapt my query changing the order as you want and post it.

From your previous topic:

Michel Cadot wrote on Tue, 21 August 2012 09:56
From your previous topics:

cookiemonster wrote on Mon, 12 March 2012 11:33
@victoryhendry - Will you please read and follow How to use [code] tags and make your code easier to read? You've been asked several times before.
...


Quote:
Michel Cadot wrote on Tue, 06 March 2012 11:50
Michel Cadot wrote on Mon, 20 February 2012 10:51
Michel Cadot wrote on Mon, 20 February 2012 10:24
With all SQL questions, 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.

Before, 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.
...

...

...



Quote:
Michel Cadot wrote on Wed, 22 February 2012 09:41
@ victoryhendry,

and it is NOT formatted.

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.

Regards
Michel

...


Regards
Michel

Re: Need a output from multilple rows return only one row in oracle? [message #580774 is a reply to message #580762] Thu, 28 March 2013 00:36 Go to previous messageGo to next message
Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates
Senior Member
Please Try this :

SELECT * FROM emp1 b 
WHERE 
 sal = ( SELECT MAX(sal) FROM emp1 a 
WHERE deptname = b.deptname )  


Regards,
Paules
Re: Need a output from multilple rows return only one row in oracle? [message #580776 is a reply to message #580774] Thu, 28 March 2013 01:25 Go to previous message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is wrong.
Please do NOT post "solution" that you didn't test.

Regards
Michel
Previous Topic: generating a key for grouping
Next Topic: Read only the input string from BLOB datatype column
Goto Forum:
  


Current Time: Sun Dec 28 08:34:08 CST 2014

Total time taken to generate the page: 0.07700 seconds