Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Query - Help


Hi,

I am quite new to SQL. I need a help with this query.

How can I get the top 3 salaries paid department wise? Sample data and table
structure below -

-- Sample Data

Create Table Emp
(
eid tinyint,
sal smallint,
dept tinyint
)

insert into emp values (1, 10, 1)
insert into emp values (2, 11, 1)
insert into emp values (3, 8, 1)
insert into emp values (4, 15, 1)
insert into emp values (5, 20, 1)
insert into emp values (11, 10, 2)
insert into emp values (21, 11, 2)
insert into emp values (31, 8, 2)
insert into emp values (41, 15, 2)
insert into emp values (51, 20, 2)

-- Required Output

Dept  Sal   Eid
1        20    5
1        15    4
1        11    2
2        20   51
2        15   41
2        11   21

Regards,
Karthik

Karthik,

how do you want to handle ties?

insert into emp values (1, 10, 1)
insert into emp values (2, 11, 1)
insert into emp values (3, 8, 1)
insert into emp values (4, 15, 1)

--- ties
insert into emp values (5, 20, 1)
insert into emp values (115, 20, 1)
insert into emp values (125, 20, 1)

AMB

Hi Alejandro,

Ties could be displayed randomly. But the total count of records should
never exceed more than 3 per department.

Thank you.

Regards,
Karthik

Here is how it can be done in SQL Server 2000 & 2005:

SELECT Dept, Sal, Eid
FROM emp AS E1
WHERE Eid IN
  (SELECT TOP 3 Eid
   FROM emp AS E2
   WHERE E2.Dept = E1.Dept
   ORDER BY Sal DESC, Eid)
ORDER BY Dept, Sal DESC, Eid

And in SQL Server 2005 only:

SELECT Dept, Sal, Eid
FROM (SELECT Dept, Sal, Eid,
              ROW_NUMBER() OVER(PARTITION BY Dept ORDER BY Sal DESC, Eid) As
rn
              FROM emp) AS E
WHERE rn <= 3

This assumes no special handling for ties.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

-----------------------------------------------Reply-----------------------------------------------

Do:

SELECT dept, sal, eid
  FROM ( SELECT eid, sal, dept,
                RANK() OVER ( PARTITION BY dept ORDER BY sal DESC )
           FROM Emp ) D ( eid, sal, dept, rank )
WHERE rank <=3 ;

--
Anith

-----------------------------------------------Reply-----------------------------------------------

Karthik,

Then you can use same approach posted by Anith, but using row_number()
instead rank().

;with cte
as
(
SELECT
   eid, sal, dept,
   ROW_NUMBER() OVER ( PARTITION BY dept ORDER BY sal DESC) as rn
FROM
   Emp
)
SELECT dept, sal, eid
FROM CTE
WHERE rn <=3 ;
go

AMB

Karthik,

I would go with the solutions posted by Plamen Ratchev, they both are
deterministic.

AMB

Thanks a lot Plamen! Works just as expected!

Anith & Alejandro - Thank you for your contributions too! Will try them when
I get a SQL 2005 environment!

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc