Gordon,
I feel as though I am your neighbor this week since we received a gift of frigid Canadian wind from your neck of the woods a few days ago.
Now to your question...There are some syntax and logic issues with your code. First I'll address those issues, then I'll suggest a better way to achieve your objective. Here is corrected code that shows that you
can have an "order by" clause in an in-line SELECT:
Code:
SELECT PreviousHire, HireDate
from (SELECT empId AS Previoushire ,hiredate
from emp e
WHERE EmpId <> E.EmpId
and HireDate < E.HireDate
and RowNum =1
ORDER BY HireDate DESC);
no rows selected
Here are the issues with your code:[ul][li]An in-line SELECT still requires a FROM clause of its own. Your in-line did not have a FROM clause, and it cannot "see" the outer SELECT's FROM clause.[/li][li]If your in-line SELECT will be providing the result set for your outer SELECT, then the outer SELECT must refer to the result set from the in-line SELECT. Your outer SELECT wanted to "see" EmpId and HireDate, therefore you need "Hiredate" returned from the in-line SELECT, and the name of the EmpId expression has been aliased to PreviousHire.[/li][li]The reason that there are "no rows selected" in the result set is because the only row that the in-line SELECT considers is "WHERE...rownum=1". In a WHERE clause, "rownum=1"
does not take the first row AFTER sorting instead, it
limits the initial result set to just the first row from the table.[/li][/ul]
Now for an alternative...(There are many, many alternatives, including the use of Oracle Analytics, but at this point, I believe that the logically simplest method is a good one to start with.) The method I'll illustrate follows this scenario:[ul][li]Identifty the EmpID of the employee that is your focus (i.e., the employee for which you are trying to locate the just-previously-hired employee)[/li][li]Find the HireDate of your focus employee.[/li][li]Find the HireDate of the employee whose HireDate is just previous to your focus employee.[/li][li]Find the employee with that just-previous HireDate[/li][/ul]Following are two pieces of code and their results:
1) A SELECT of the dataset, sorted in HIREDATE descending order, that I'm using to simulate your environment.
2) Code that prompts for an EmpID of the employee that is your starting focus (...the employee for which you are looking for a just-previouly-hired employee). Then, based upon the focus employee, the code SELECTs the previously hired employee. Since there is a SQL*Plus ACCEPT statement in the code, one cannot simply cut-and-paste the ACCEPT and the SELECT statement directly to a SQL*Plus prompt. Instead the code must run from a script. In this case, I have saved the code to a script named TEMP.SQL, which I then invoke from the SQL*Plus prompt:
Code:
select empid, last_name, hiredate from emp order by hiredate desc;
EMPID LAST_NAME HIREDATE
----- --------------- ---------
9 Catchpole 09-FEB-92
16 Maduro 07-FEB-92
14 Nguyen 22-JAN-92
12 Giljum 18-JAN-92
15 Dumas 09-OCT-91
19 Patel 06-AUG-91
20 Newman 21-JUL-91
3 Nagayama 17-JUN-91
21 Markarian 26-MAY-91
25 Schwartz 09-MAY-91
24 Dancs 17-MAR-91
10 Havel 27-FEB-91
13 Sedeghi 18-FEB-91
18 Nozaki 09-FEB-91
6 Urguhart 18-JAN-91
22 Chang 30-NOV-90
23 Patel 17-OCT-90
7 Menchu 14-MAY-90
11 Magee 14-MAY-90
4 Quick-To-See 07-APR-90
8 Biri 07-APR-90
17 Smith 08-MAR-90
5 Ropeburn 04-MAR-90
1 Velasquez 03-MAR-90
2 Ngao 08-MAR-90
25 rows selected.
Code:
(Contents of TEMP.SQL):
accept focus_emp_id prompt "Enter the EmpID of the employee that was hired just after the employee you wish to see: "
select EmpID PreviousHire, Hiredate
from emp
where HireDate = (select max(HireDate) from Emp
where hiredate < (select HireDate
from emp
where EmpID = &focus_emp_id))
/
SQL> @TEMP.SQL
Enter the EmpID of the employee that was hired just after the employee you wish to see: 3
PREVIOUSHIRE HIREDATE
------------ ---------------
21 26-MAY-91
1 row selected.
SQL> @TEMP
Enter the EmpID of the employee that was hired just after the employee you wish to see: 22
PREVIOUSHIRE HIREDATE
------------ ---------------
23 17-OCT-90
1 row selected.
By comparing the original set of employees, sorted by HIREDATE, you can see that TEMP.SQL does what you want.
If you have additional questions, please post.
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.
