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 V
It 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.codecomments.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.googlegr oups.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
>
|||hi Amish
thanks for ur reply
but,for ur first query
Select max(salary), employeename, FKDeptID from employee_sarda group by
employeename,FKDeptID
its just listing all the records
and for ur second query
Select max(salary), max(employeename), FKDeptID from employee_sarda
group by FKDeptID
but whats it meant by max(employeename) its giving the salary field
correctly
but it only selects the first employeename not the required employeename
the required empname is the employeename corresponding to max salary in
that particular dept
Thanks and regards
Sarada v
*** Sent via Developersdex http://www.codecomments.com ***
|||hi
this is the table structure
PKEmployeeID bigint
EmployeeName varchar
FKDeptID bigint
Salary decimal
PhoneNumber varchar
FKEmployeeID bigint
and i dont know anything about creating functions and all those stuff
i am loooking for a simple query
Can u help me plz
Thanks and Regards
Sarada V
*** Sent via Developersdex http://www.codecomments.com ***
|||Try this: ("nested table" techniqe)
select emp.employeename, maxsal.salary1, maxsal.fkdeptid from
employee_sarda,
( Select max(salary) as salary1, FKDeptID from employee_sarda
group by FKDeptID ) as maxsal
where
emp.FKDeptid = maxsal.FKDeptid
emp.salary = maxsal.salary
Note: If 2 or more guys of a dept. earn the same maximum, you'll get
back all of them.
Is this from some exam :-) ?
|||Or this ("correlated subquery")
select emp.employeename, emp.salary, emp.fkdeptid from
employee_sarda emp
where salary =
(select max(salary) from employee_sarda maxsal
where emp.fkdeptid = maxsal.fkdeptid)
|||sorry other option may be like this
select salary , employeename, fkdeptid from employee_sarda e where
salary in (select max(salary) from employee_sarda t where e.fkdeptid =
t.fkdeptid )
or
select salary , employeename, fkdeptid from employee_sarda e where
exists (select max(salary),fkdeptid from employee_sarda t group by
fkdeptid having max(t.salary)= e.salary and e.fkdeptid = t.fkdeptid )
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||Sorry other option may be like this
select salary , employeename, fkdeptid from employee_sharda e where
salary in (select max(salary) from employee_sharda t1 where t1.fkdeptid
= e.fkdeptid )
or
select salary , employeename, fkdeptid from employee_sharda e where
exists (select max(salary),fkdeptid from employee_sharda t1 group by
fkdeptid having max(t1.salary)= e.salary and t1.fkdeptid = e.fkdeptid )
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||Hello Amish,
Great ones, too.
Shalll we now elaborate on the "query plans" of the optimizer for all
these
to determine which is the "quickest" (best ?) :-))
sql

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.codecomments.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
>|||hi Amish
thanks for ur reply
but,for ur first query
Select max(salary), employeename, FKDeptID from employee_sarda group by
employeename,FKDeptID
its just listing all the records
and for ur second query
Select max(salary), max(employeename), FKDeptID from employee_sarda
group by FKDeptID
but whats it meant by max(employeename) its giving the salary field
correctly
but it only selects the first employeename not the required employeename
the required empname is the employeename corresponding to max salary in
that particular dept
Thanks and regards
Sarada v
*** Sent via Developersdex http://www.codecomments.com ***|||hi
this is the table structure
PKEmployeeID bigint
EmployeeName varchar
FKDeptID bigint
Salary decimal
PhoneNumber varchar
FKEmployeeID bigint
and i dont know anything about creating functions and all those stuff
i am loooking for a simple query
Can u help me plz
Thanks and Regards
Sarada V
*** Sent via Developersdex http://www.codecomments.com ***|||Try this: ("nested table" techniqe)
select emp.employeename, maxsal.salary1, maxsal.fkdeptid from
employee_sarda,
( Select max(salary) as salary1, FKDeptID from employee_sarda
group by FKDeptID ) as maxsal
where
emp.FKDeptid = maxsal.FKDeptid
emp.salary = maxsal.salary
Note: If 2 or more guys of a dept. earn the same maximum, you'll get
back all of them.
Is this from some exam :-) '|||Or this ("correlated subquery")
select emp.employeename, emp.salary, emp.fkdeptid from
employee_sarda emp
where salary =
(select max(salary) from employee_sarda maxsal
where emp.fkdeptid = maxsal.fkdeptid)|||sorry other option may be like this
select salary , employeename, fkdeptid from employee_sarda e where
salary in (select max(salary) from employee_sarda t where e.fkdeptid =
t.fkdeptid )
or
select salary , employeename, fkdeptid from employee_sarda e where
exists (select max(salary),fkdeptid from employee_sarda t group by
fkdeptid having max(t.salary)= e.salary and e.fkdeptid = t.fkdeptid )
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||Sorry other option may be like this
select salary , employeename, fkdeptid from employee_sharda e where
salary in (select max(salary) from employee_sharda t1 where t1.fkdeptid
= e.fkdeptid )
or
select salary , employeename, fkdeptid from employee_sharda e where
exists (select max(salary),fkdeptid from employee_sharda t1 group by
fkdeptid having max(t1.salary)= e.salary and t1.fkdeptid = e.fkdeptid )
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||Hello Amish,
Great ones, too.
Shalll we now elaborate on the "query plans" of the optimizer for all
these
to determine which is the "quickest" (best ?) :-))

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
>

how can i manage database on lan base[network base]software

hi
my self avi
currently i am developing one application in vb 6 and back end as
sqlserver 7 which is used on lan.
i have some problem. like in my database i have one table salesvoucher
which has 'voucherno' field. when 2-3 user will work on salesform at a
time [since the softwrae will run on lan] then the same voucherno will
save for all users data which is wrong i need to save different voucherno
for each no. is there any way in sqlserver to apply some condition on
database or to set some its property[tables] so that at a time only one
users data will save depend on firstcome first serve base or is there any
way to make changes in programme.
plz help me regarding this i need it very badly.
since its my first lan based software plz give me some books name
regarding this software for vb6 and sqlserver7"avinash" <pawar_avinash@.rediffmail.com> wrote in message
news:00a8643e7e3050f1ff2f4ae6aa7209df@.localhost.ta lkaboutdatabases.com...
> hi
> my self avi
> currently i am developing one application in vb 6 and back end as
> sqlserver 7 which is used on lan.
> i have some problem. like in my database i have one table salesvoucher
> which has 'voucherno' field. when 2-3 user will work on salesform at a
> time [since the softwrae will run on lan] then the same voucherno will
> save for all users data which is wrong i need to save different voucherno
> for each no. is there any way in sqlserver to apply some condition on
> database or to set some its property[tables] so that at a time only one
> users data will save depend on firstcome first serve base or is there any
> way to make changes in programme.
> plz help me regarding this i need it very badly.
> since its my first lan based software plz give me some books name
> regarding this software for vb6 and sqlserver7

If the voucherno must be unique, then you should put a primary key or unique
constraint on that column, so that it's impossible to have duplicates. If
the voucherno is generated on the client, then you would have to handle the
error raised when a duplicate is inserted (such as error 2627 for primary
key violation), generate a new number, and submit it again.

Alternatively, you could generate the voucherno in the database. If you just
need an integer value, then you can use the IDENTITY property to generate
the number for you - see "IDENTITY (Property)" in Books Online. Every time
you insert a new row, MSSQL will generate a new number for you - this avoids
writing any code to generate new numbers.

Regarding books, you might find some useful information about SQL books
here:

http://vyaskn.tripod.com/sqlbooks.htm

Someone else may be able to suggest a good VB/SQL book, or you might want to
post in a VB group.

Simon|||Dear Avanish... sorry I cannot help you in dat... while I m just wanna
post a msg here... BYE