Query Optimization

IS there any way to rewrite this Query in optimized way?

SELECT dbo.Table1.EmpId E from dbo.Table1
where EmpId in(
SELECT dbo.Table1.EmpId
FROM (SELECT DISTINCT PersonID, MAX(dtmStatusDate) AS dtmStatusDate
FROM dbo.Table1
GROUP BY PersonID) derived_table INNER JOIN
dbo.Table1 ON derived_table.PersonID = dbo.Table1.PersonID AND
derived_table.dtmStatusDate = dbo.Table1.dtmStatusDate))

Thanks...jDon't know abiut being faster but I think this is what oyu are trying to do. (get the empid's with max(dtmStatusDate) from each person.

SELECT t1.EmpId
from dbo.Table1 t1
where t1.dtmStatusDate =
(select max(dtmStatusDate) from dbo.Table1 t2 where t1.PersonID = t2.PersonID)

also try

SELECT t1.EmpId
from dbo.Table1 t1
where not exists ( select * from dbo.Table1 t2 where t1.PersonID = t2.PersonID and t1.dtmStatusDate < t2.dtmStatusDate)

query on procedure

Hi,

Please see the below procedure.

create procedure a1
as
begin

create table #table1
{
empid int;
empname varchar
}
insert into #table1 select empid,empname from employee where
empcode='50'

select e.* from employee e, #table1 as t1 where e.empid=t1.empid and
e.empname=t1.empname; /* query1 */

end

In location query1,empid , empname in #table1 substitutes all the
values at the time, i need to substitute each value individually in
location query1.

Is there any way to do this?meendar wrote:

Quote:

Originally Posted by

create procedure a1

I trust your production code will have meaningful procedure names.

Quote:

Originally Posted by

create table #table1
{
empid int;
empname varchar
}

Should be

empid int,
empname varchar(30) -- or whatever

Quote:

Originally Posted by

insert into #table1 select empid,empname from employee where
empcode='50'
>
select e.* from employee e, #table1 as t1 where e.empid=t1.empid and
e.empname=t1.empname; /* query1 */

Why are you doing this, instead of simply

select * -- you should really have an explicit list of fields
from employee
where empcode = '50'

Does the 'employee' table really have both empcode and empid? If
so, then are they both enforced as unique?

Quote:

Originally Posted by

In location query1,empid , empname in #table1 substitutes all the
values at the time, i need to substitute each value individually in
location query1.
>
Is there any way to do this?

I don't understand what you mean. Please provide an example of what
it does now, and of what you want it to do instead.