CODEWITHSUNDEEP
sqloracle-database
user466534
user466534

Reputation:

Select most early coming employees

I have the following problem:

select most early coming employees for every location city,show location city and coming date

I have tried following one

select  e.hiredate  as LOC,d.loc  as COMDATE
from    emp e, dept  d
where   e.hiredate in (
    select  min(e.hiredate) 
    from  emp e
    group by d.loc
)

it shows following result

17-DEC-80   NEW YORK
17-DEC-80   DALLAS
17-DEC-80   CHICAGO
17-DEC-80   BOSTON

but problem is that there is only one 17-DEC-80,so it means that it repeats itself, how could I fix it?

Upvotes: 0

Views: 94

Answers (3)

Art
Art

Reputation: 5782

You have a cross join or Cartesian product in your query as not joining emp and dept tables. Simpler version of first query by JW. And aliases are not correct...:

select  e.hiredate  as LOC,d.loc  as COMDATE
 from    scott.emp e, scott.dept  d
 where e.deptno= d.deptno -- Cartesian if you not join --
 and e.hiredate in (
     select  min(e.hiredate) 
      from  scott.emp e
      group by d.loc
   )
  /

Upvotes: 1

DazzaL
DazzaL

Reputation: 21973

if you need to select other fields along with the hiredate/location you can use the rank() analytic:

select *
  from (select e.*, d.loc, rank() over (partition by d.loc order by e.hiredate) rnk
          from emp e
               inner join dept d
                       on d.deptno = e.deptno
       )
 where rnk = 1;

if you only needed hiredate and location then just use min()

select min(e.hiredate), d.loc
  from emp e
       inner join dept d
               on d.deptno = e.deptno
 group by d.loc;

Upvotes: 1

John Woo
John Woo

Reputation: 263723

the result is a product from cross join, you need to supply the relationship between the tables. ex

SELECT  e.hiredate AS LOC,
        d.loc AS COMDATE
FROM    emp e
        INNER JOIN dept d 
            ON e.deptno = d.deptno
WHERE   e.hiredate IN 
        (
            SELECT min(e.hiredate)
            FROM emp e
            GROUP BY d.loc
        )

Upvotes: 1

Related Questions