OraFAQ Forum: SQL & PL/SQL » Subquery with multiple rows

Home » SQL & PL/SQL » SQL & PL/SQL » Subquery with multiple rows (oracle db 10g)

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

Subquery with multiple rows [message #637541]

Tue, 19 May 2015 22:50

aruntutor
Messages: 10
Registered: June 2014
Location: chennai

Junior Member

hi all,

in below query, am getting duplicate rows in the result, whereas not suppose to be. Sad

when i tried to execute the inner query, am getting the distinct row Smile

but when am running the whole query, am getting duplicate salary and department_ids. Sad

not able to find root cause for it. Kindly help me in finding out the root cause.

--Display employees getting min salary in each department.

select salary, department_id
from employees
where salary in
(select min(salary)
from employees
group by department_id)
order by 2;

Salary Department_id
4400 10
6000 20
2500 30
6500 40
2500 50
2500 50
2500 50
2500 50
2500 50
4200 50
6500 50
2100 50
6000 60
4200 60
10000 70
7000 80
10000 80
10000 80
7000 80
10000 80
6100 80
17000 90
17000 90
6900 100
8300 110

[Updated on: Tue, 19 May 2015 22:52]

Report message to a moderator

Re: Subquery with multiple rows [message #637542 is a reply to message #637541]

Tue, 19 May 2015 22:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/


  1  select min(salary), department_id
  2  from employees
  3  group by department_id
  4* order by 2
SQL> /

MIN(SALARY) DEPARTMENT_ID
----------- -------------
       4400            10
       6000            20
       2500            30
       6500            40
       2100            50
       4200            60
      10000            70
       6100            80
      17000            90
       6900           100
       8300           110

MIN(SALARY) DEPARTMENT_ID
----------- -------------
       7000

12 rows selected.

SQL>

[Updated on: Tue, 19 May 2015 23:21]

Report message to a moderator

Re: Subquery with multiple rows [message #637545 is a reply to message #637541]

Tue, 19 May 2015 23:53

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

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.

In the end, feedback and thank people who spent time to help you.

Report message to a moderator

Re: Subquery with multiple rows [message #637548 is a reply to message #637541]

Wed, 20 May 2015 01:00

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Hi,
aruntutor wrote on Wed, 20 May 2015 05:50

in below query, am getting duplicate rows in the result, whereas not suppose to be.

Based on what? Making your own SQL rules instead of the described e.g. in SQL Language Reference?
aruntutor wrote on Wed, 20 May 2015 05:50

not able to find root cause for it. Kindly help me in finding out the root cause.

The root cause is not understanding basic principles of SQL language.

The inner query is aggregate one, so it returns one row per aggregate column/expression(s).
One or more rows in the input make one row in the output.

The outer one is non-aggregate query on one table/view - it returns each row from that table/view which satisfies its condition.
One row in the input make one row in the output.

One magic: what about this query:

select department_id from employees;

? It also returns "duplicates" - as many rows with the same DEPARTMENT_ID as many employees are there. If it should not, what would be the use of GROUP BY clause then?
aruntutor wrote on Wed, 20 May 2015 05:50

--Display employees getting min salary in each department.

What about displaying something which uniquely identifies each employee? There may be (and are) multiple ones in the same department with the same salary.

Report message to a moderator

Previous Topic:	Dirty read issues
Next Topic:	Round Robin implementaion through sql script

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 30 09:03:33 CDT 2026