May 14, 2002 at 2:35 pm
Results expected are:
Top 2 employees of each department having maximum salaries in their respective departments.
this query seems to be simple, while reading, but its not that easy. I have tried enough for the day and so posting the query here. I shall post you my work tomm as I have forgotten the saved queries on my office desktop today. The table structures and data are mentioned below:
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
May 14, 2002 at 2:37 pm
if exists (select * from sysobjects where id = object_id(N'[dbo].[Dept]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Dept]
GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[Emp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Emp]
GO
CREATE TABLE [dbo].[Dept] (
[deptid] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[deptname] [varchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Emp] (
[empid] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[empname] [varchar] (50) NOT NULL ,
[deptid] [numeric](18, 0) NOT NULL ,
[salary] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
SET IDENTITY_INSERT emp ON
----
GO
-------------------------------------------------------------------------
-- The table name is: emp
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO emp VALUES (13,"vijay",1,4000)
INSERT INTO emp VALUES (14,"ajay",1,3000)
INSERT INTO emp VALUES (15,"sanjay",1,3000)
INSERT INTO emp VALUES (16,"hitesh",1,4000)
INSERT INTO emp VALUES (17,"paras",2,5000)
INSERT INTO emp VALUES (18,"kiran",2,7000)
INSERT INTO emp VALUES (19,"gautam",2,7000)
INSERT INTO emp VALUES (20,"abhi",2,8000)
INSERT INTO emp VALUES (21,"jhon",3,9000)
INSERT INTO emp VALUES (22,"ajit",3,10000)
INSERT INTO emp VALUES (23,"amit",3,11000)
INSERT INTO emp VALUES (24,"sunilr",3,12000)
----
GO
--------------------------------------------------------------------------
SET IDENTITY_INSERT emp OFF
----
GO
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
SET IDENTITY_INSERT dept ON
----
GO
-------------------------------------------------------------------------
-- The table name is: dept
-------------------------------------------------------------------------------------------------------------
INSERT INTO dept VALUES (1,"Home")
INSERT INTO dept VALUES (2,"Office1")
INSERT INTO dept VALUES (3,"Office2")
----
GO
--------------------------------------------------------------------------
SET IDENTITY_INSERT dept OFF
----
GO
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
May 14, 2002 at 3:10 pm
SELECT
*
FROM
Emp oQ
INNER JOIN
Dept
ON
oQ.deptid = Dept.deptid
WHERE
Salary IN (SELECT TOP 2 Salary FROM Emp iQ WHERE iQ.deptid = oQ.deptid ORDER BY Salary DESC)
ORDER BY
deptname
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 14, 2002 at 9:55 pm
But by this query you are getting 3 people from dept 2. Only 2 wanted
13vijay140001Home
16hitesh140001Home
18kiran270002Office1
19gautam270002Office1
20abhi280002Office1
23amit3110003Office2
24sunilr3120003Office2
quote:
SELECT*
FROM
Emp oQ
INNER JOIN
Dept
ON
oQ.deptid = Dept.deptid
WHERE
Salary IN (SELECT TOP 2 Salary FROM Emp iQ WHERE iQ.deptid = oQ.deptid ORDER BY Salary DESC)
ORDER BY
deptname
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Paras Shah
Evision Technologies
Mumbai, India
Edited by - paras_98 on 05/14/2002 9:59:30 PM
Paras Shah
Evision Technologies
Mumbai, India
May 15, 2002 at 4:20 am
Try these then, you should be able to figure out which Emp comes thru based on order:
SELECT
*
FROM
Emp oQ
INNER JOIN
Dept
ON
oQ.deptid = Dept.deptid
WHERE
Salary IN (SELECT TOP 2 Salary FROM Emp iQ WHERE iQ.deptid = oQ.deptid ORDER BY Salary DESC, EmpID) AND
EmpID IN (SELECT TOP 2 EmpID FROM Emp iQ WHERE iQ.deptid = oQ.deptid ORDER BY Salary DESC, EmpID)
ORDER BY
deptname
Or This one which is a little odd but has the better execution plan
SELECT
*
FROM
Emp oQ
INNER JOIN
Dept
ON
oQ.deptid = Dept.deptid
WHERE
EmpID + Cast(Salary AS VARCHAR) IN (SELECT TOP 2 EmpID + Cast(Salary AS VARCHAR) FROM Emp iQ WHERE iQ.deptid = oQ.deptid ORDER BY Salary DESC, EmpID)
ORDER BY
deptname
Edited by - antares686 on 05/15/2002 04:29:46 AM
May 15, 2002 at 3:10 pm
The first one seems to be perfect!
Thankx a lot!
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply