Display name and hiredate

[shebbycs]

I had data like this

 

empno  ename       job              mgr   hiredate          sal     comm   deptno

7369     SMITH     CLERK        7902  1980-12-17    800     NULL     20

7499     ALLEN    SALESMAN  7698  1981-02-20   1600    300       30 

7521     WARD    SALESMAN  7698  1981-02-22    1250   500       30

7566     JONES    MANAGER   7839  1981-04-02    2975   NULL    20

 

1) Display the name and hire date of the employee who was hired first without using subquery

 

2) Display the name and hire date of the employee who was hired last without using subquery

 

 

[Psycho]

And what have you tried so far and/or what questions do you have? this forum is for people to get help with code they have written, not for people to do your homework for you. Unless your instructor is a moron I suspect he has recently gone over several different SQL functions recently. Go over those and give it a try.

[shebbycs]

And what have you tried so far and/or what questions do you have? this forum is for people to get help with code they have written, not for people to do your homework for you. Unless your instructor is a moron I suspect he has recently gone over several different SQL functions recently. Go over those and give it a try.

 

I had try for the date i using for max and min but when to call together with name I did not manage to make it because it will showing many name since i used the group by that part I stuck

[Psycho]

You want a single record. GROUP BY is used to "group" records to get things such as the SUM, AVG, etc across many records. There are two specific functions you will want to use to get the records in a particular order and to select only the one you want for each query.

[shebbycs]

You want a single record. GROUP BY is used to "group" records to get things such as the SUM, AVG, etc across many records. There are two specific functions you will want to use to get the records in a particular order and to select only the one you want for each query.

 

ok let said I had called the min hiredate for the first hired how can i call that person only ?

because if i did like min hiredate and min ename it will not give the first hired date person

[Barand]

Have you considered sorting the records and just returning the first record?

[Psycho]

ok let said I had called the min hiredate for the first hired how can i call that person only ?

because if i did like min hiredate and min ename it will not give the first hired date person

 

MIN() is another GROUP BY function. This is not a problem to be resolved with grouping. See Barand's response above. He decided to throw you a bone.

[shebbycs]

Have you considered sorting the records and just returning the first record?

 

  SELECT HIREDATE

  FROM EMP

  WHERE ROWNUM<=1

  ORDER BY HIREDATE; ? is it like this?

[shebbycs]

but how to call first and last in sql developer if first and last is invalid identifier

[Psycho]

Not quite. The ORDER BY will ensure you are getting the records in order so the newest is first or the oldest is first (based on whether you sort in ascending or descending). But, to get the first record you want to use LIMIT

 

 

  SELECT HIREDATE
  FROM EMP
  ORDER BY HIREDATE ASC <-- or "DESC"
  LIMIT 1

[Barand]

You were asked for the name too!