Home » Other » Client Tools » Query
Query [message #25914] Mon, 26 May 2003 06:04 Go to next message
Prabodh
Messages: 2
Registered: May 2003
Junior Member
I have one Question regarding DBMS, it may be Oracle or SQL Server.

Suppose we have EMP table with columns as EmpID, EName and Salary.
It has records as .......

EmpID EName Salary.
1 ABC 1000
3 XYZ 5000
5 PQR 10000
100 UVW 10000

I want to get the last but one (Second Last) record from the table. How can I do that?
Re: Query [message #25916 is a reply to message #25914] Mon, 26 May 2003 06:43 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
What do you mean 'second last'. One property of a relational database is that the data in a table is NOT ordered. It really is not predictable where a record would end up. So, unless you have a column to order by, there's no telling what you want. Let's say you want the one with the second highest salary, than you could do the following:
select ename
    ,  salary
  FROM ( SELECT emp.*
              , rownum rn
           FROM emp
          ORDER BY salary DESC
       ) e
 WHERE e.rn = 1
/
If you have Oracle 8.1.7 or above (I doubt whether it works in previous versions) you could use analytic functions:
SELECT ename, salary,
   RANK() OVER (ORDER BY salary DESC, ENAME) as rk
   FROM emp WHERE rank = 2


MHE
Re: Query [message #25921 is a reply to message #25916] Mon, 26 May 2003 23:24 Go to previous messageGo to next message
Prabodh
Messages: 2
Registered: May 2003
Junior Member
Thanks for your reply.
I have also got one answer, where it is suppposed to be ordered by empID.

select * from Emp where EmpID=
(
select max(EmpID)
from Emp
where EmpID<(select max(EmpID) from Emp)
)

But what if data is not in ordered format?
Can we do something using RowID? if yes How?

Thanks,
Prabodh
sorting data in Oracle: can't use ROWID! [message #25924 is a reply to message #25921] Tue, 27 May 2003 01:07 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
The solution given also works, but is more limited. It only works if you're looking for the second highest empID. But it works. As usual in SQL, there are several ways to accomplish the desired result. You just need to look at performance. What option is the most performant one in YOUR environment?

Now, ROWID is not an option since it is the physical location of a record. You cannot see it as a sequence number. Sometimes you read about people who claim that ROWID is the way to go, and they even might give you an example, but trust me on this one: you cannot rely on ROWID to sort your data. I believe Tom Kyte has explained it (it is about the last record, but you'll catch the drift). If you want to order you need to have something to order your data by. Typically you would create some audit fields (at least we do): creation_date, creation_user, modification_date, modification_user (of course the dates are more important (since they are used to sort our data) than the "user" fields). The audit fields are filled automatically unsing triggers.

MHE
Previous Topic: help with outer join sql -
Next Topic: Finding Largest Among Matrix
Goto Forum:
  


Current Time: Thu Mar 28 19:20:34 CDT 2024