February 21, 2007 at 12:10 am
hi!
i have a table with two fields.
a- employee_id
b- salary.
data is like this.
employee_id ----- salary
1001-------------5000
1002-------------6000
1003-------------7500
1004-------------4000
1005-------------5500
how can i come to knw the second highest value from this table. if posible tell me the query which can run on both sql server and oracle server too.
regards.
atif saeed khan
Kindest Regards,
Atif Saeed Khan
February 21, 2007 at 3:15 am
select distinct(sal) from emp where sal >= (select max(sal) from emp where
sal <(select max(sal) from emp where sal< (select max(sal) from emp)))
The following Query will display emps having salary in top 3
select * from emp where sal >= (select max(sal) from emp where sal <(select
max(sal) from emp where sal< (select max(sal) from emp)))
February 21, 2007 at 3:21 am
select max(a.sal),max(b.sal),max(c.sal) from emp a , emp b, emp c
where a.sal>b.sal and b.sal>c.sal
Dear Newbie. inshallah this query will give u a accurate result.
Reagrds
Syed Muhammad Naveed
Database Administrator
Saudi Pak Bank Karachi ,Pakistan
February 21, 2007 at 3:21 am
Here a quick and dirty way....
(depending how many rows are in employee)
CREATE TABLE [dbo].[TEMP#] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[employee_id] [int] NULL ,
[Salary] [int] NULL
) ON [PRIMARY]
INSERT [dbo].[TEMP#](employee_id, Salary)
SELECT employee_id, Salary
FROM Employee
ORDER BY Salary DESC
SELECT * FROM TEMP# WHERE [ID] =2
drop table [dbo].[TEMP#]
Andy.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply