How Can I retrieve data from a self joined table

hi all
i have one table with the format
PKEmployeeID PrimaryKey
FKDeptID
Salary
EmployeeName
FKEmployeeID
here I have to retrieve the max salary by dept wise
thats ok by writing
Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
How can i retrieve the employeename for the max salary
Thanks and Regards
Sarada VIt may be possible in same FKDeptID two or more employee have same
salary which is max in that FKDeptID.
Select max(salary), employeename, FKDeptID from employee_sarda group by
employeename,FKDeptID.
or
if you want only one name then
Select max(salary), max(employeename), FKDeptID from employee_sarda
group by FKDeptID.
Please post ddl with data to get better idea.
Regards
Amish
*** Sent via Developersdex http://www.developersdex.com ***|||Hi
Can you provide us with your table's stucture?
--Written by Itzik Ben-Gan
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
CREATE FUNCTION dbo.ufn_GetSubtreeSalary
(
@.mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN (SELECT Salary
FROM Employees WHERE empid = @.mgrid) +
CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
(SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
FROM Employees
WHERE mgrid = @.mgrid)
ELSE 0
END
END
GO
SELECT dbo.ufn_GetSubtreeSalary(3)
GO
If it doesnot help , pls show us an expected result
"pandu" <saradabhanuv@.gmail.com> wrote in message
news:1135835404.968676.316380@.g14g2000cwa.googlegroups.com...
> hi all
> i have one table with the format
> PKEmployeeID PrimaryKey
> FKDeptID
> Salary
> EmployeeName
> FKEmployeeID
> here I have to retrieve the max salary by dept wise
> thats ok by writing
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
> Thanks and Regards
> Sarada V
>