OraFAQ Forum: Client Tools

Home » Other » Client Tools » Query

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

Query [message #25914]

Mon, 26 May 2003 06:04

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?

Report message to a moderator

Re: Query [message #25916 is a reply to message #25914]

Mon, 26 May 2003 06:43

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

Report message to a moderator

Re: Query [message #25921 is a reply to message #25916]

Mon, 26 May 2003 23:24

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

Report message to a moderator

sorting data in Oracle: can't use ROWID! [message #25924 is a reply to message #25921]

Tue, 27 May 2003 01:07

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 18 11:06:47 CST 2025