The title says it all. Given that I have created a login for a domain group, and a database user for that login. What I want to do is retrieving the domain username for the active user. USER_NAME retrieves the database username, suser_name returns (of course) NULL as this is not a sql user.
The goal is to use domain group logins, while still allowing for logging what user performed which action.USER_NAME retrieves the database username, suser_name returns (of course) NULL as this is not a sql user.
Really?
When I query suser_sname() while logged in under a windows domain account (using a windows domain group login) I get my DOMAIN\UserName.
Regards,
hmscott
Friday, March 30, 2012
How can I retrieve Description and other column information?
Hi, all..
I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
Is there any sp for this or any query for this?
Thank you all...type
sp_help tablename|||select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
See BOL for more information about schema.|||You can also use
INFORMATION_SCHEMA.columns
cheers
-ss|||I too would like to get at the Description and I tried all of the above but Description doesn't seem to be there. I looked at all the INFORMATION_SCHEMA views and found nothing. I checked the system tables for the DB in question and found nothing. Could the Description be something internal to EM that isn't available to clients?|||What do you mean by description?|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.|||OK...I usually maitain my data dictionary separatley...
You need to look up
fn_listextendedproperty
Never really used it....|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.
Check this table:
select * from sysproperties|||Yup... Works fine.
Thanks Brett..|||select * from sysproperties
Works as well but may take extra coding to figure out the id and smallid so the data for the correct column can be identified.
Thanks...|||It works!!! Assuming a Table name of 'KB' and a column named 'Title' this gets the description.
declare @.tbid integer
declare @.colid integer
Select @.tbid=id from sysobjects where xtype='u' and name='KB'
Select @.colid = colid from syscolumns Where id=@.tbid And name='Title'
select value from sysproperties where id=@.tbid and smallid=@.colid and name='MS_Description'
Thanks snail for the pointer to sysProperties.|||Thanks all..
I think we have to know sysproperties table joined with which table..|||Can't help with the JOIN. There are three tables here and I haven't figured out how to JOIN two yet! :)|||Now I am finishing ...
This works! the following three tables joined gives what I asked.
I hope this helps all...
Thank you all for considering
Use Northwind
SELECT sysobjects.name AS [Table], syscolumns.name AS [Column], sysproperties.[value] AS Description
FROM (sysobjects INNER JOIN syscolumns
ON syscolumns.id = sysobjects.id) LEFT JOIN sysproperties
ON sysproperties.smallid = syscolumns.colid AND
sysproperties.id = syscolumns.id
where sysobjects.name = 'Orders'
-- PS
sysobjects.name : table Name (WHERE sysobjects.xtype = 'U')
sysobjects.id : table ID
sysproperties.smallid : Column id
sysproperties.[value] : Description
You can add following
WHERE sysobjects.xtype = 'U' xtype='U' means only select Table object
I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
Is there any sp for this or any query for this?
Thank you all...type
sp_help tablename|||select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
See BOL for more information about schema.|||You can also use
INFORMATION_SCHEMA.columns
cheers
-ss|||I too would like to get at the Description and I tried all of the above but Description doesn't seem to be there. I looked at all the INFORMATION_SCHEMA views and found nothing. I checked the system tables for the DB in question and found nothing. Could the Description be something internal to EM that isn't available to clients?|||What do you mean by description?|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.|||OK...I usually maitain my data dictionary separatley...
You need to look up
fn_listextendedproperty
Never really used it....|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.
Check this table:
select * from sysproperties|||Yup... Works fine.
Thanks Brett..|||select * from sysproperties
Works as well but may take extra coding to figure out the id and smallid so the data for the correct column can be identified.
Thanks...|||It works!!! Assuming a Table name of 'KB' and a column named 'Title' this gets the description.
declare @.tbid integer
declare @.colid integer
Select @.tbid=id from sysobjects where xtype='u' and name='KB'
Select @.colid = colid from syscolumns Where id=@.tbid And name='Title'
select value from sysproperties where id=@.tbid and smallid=@.colid and name='MS_Description'
Thanks snail for the pointer to sysProperties.|||Thanks all..
I think we have to know sysproperties table joined with which table..|||Can't help with the JOIN. There are three tables here and I haven't figured out how to JOIN two yet! :)|||Now I am finishing ...
This works! the following three tables joined gives what I asked.
I hope this helps all...
Thank you all for considering
Use Northwind
SELECT sysobjects.name AS [Table], syscolumns.name AS [Column], sysproperties.[value] AS Description
FROM (sysobjects INNER JOIN syscolumns
ON syscolumns.id = sysobjects.id) LEFT JOIN sysproperties
ON sysproperties.smallid = syscolumns.colid AND
sysproperties.id = syscolumns.id
where sysobjects.name = 'Orders'
-- PS
sysobjects.name : table Name (WHERE sysobjects.xtype = 'U')
sysobjects.id : table ID
sysproperties.smallid : Column id
sysproperties.[value] : Description
You can add following
WHERE sysobjects.xtype = 'U' xtype='U' means only select Table object
How can I retrieve data?
Here is my sql procedure:
ALTER PROCEDURE dbo.SoftWareShow
/*
(
@.parameter1 int = 5,
@.parameter2 datatype OUTPUT
)
*/
@.SoftID uniqueidentifier
AS
SELECT [SoftID], [SoftName], [SoftJoinDate], [SoftSize], [SoftMode], [SoftRoof], [SoftHome], [SoftDemo], [SoftFirstClassID], [SoftSecondClassID], [SoftDesc], [SoftReadCount], [SoftDownCount],ltrim(rtrim([SoftUrlOne])) SoftUrlOne, ltrim(rtrim([SoftUrlTwo])) SoftUrlTwo, ltrim(rtrim([SoftUrlThree])) SoftUrlThree, ltrim(rtrim([SoftUrlFour])) SoftUrlFour FROM [SoftWare] WHERE ([SoftID] = @.SoftID)
RETURN
where I retrieve data using sqldatasource, an error appear. how can do ?
(2) you have declared @.parameter2 as OUTPUT type but you dont seem tobe returning anything through it. So you can get rid of it.
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
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 ?) :-))
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
>
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 retrieve a recordset from a matrix-like table?
I have a table defined as (int Row, int Column, money Data); as it were a matrix.
Which is the more efficient way of retrieving a result set with the following form?
Column1 Column2 Column3
---- ---- ----
Data11 Data12 Data13
Data21 Data22 Data23
Data31 Data32 Data33
... ... ...
Thanks a lot in advance.Lookup "Crosstab queries" in Books Online, and you will see a perfect example of how to do what you want to do.
Which is the more efficient way of retrieving a result set with the following form?
Column1 Column2 Column3
---- ---- ----
Data11 Data12 Data13
Data21 Data22 Data23
Data31 Data32 Data33
... ... ...
Thanks a lot in advance.Lookup "Crosstab queries" in Books Online, and you will see a perfect example of how to do what you want to do.
Subscribe to:
Posts (Atom)