need to write complex query without cursor

Hi all,
I am trying to do the following and am getting stuck.Your help wil be higly
appreciated.
I am matching two tables.If i get a single matching row then get that single
row
but if i get >1 matching rows i should select only the one with the latest
date.
for example
Table1
--
id value
1 10
2 20
3 30
Table 2
--
id param updateDate
1 10 day1
1 20 day2
1 25 day3
2 20 day2
2 40 day 3
3 30 day 2
(day1 <day2<day3 etc)
So i should get results as
Result
--
1 25 day3
2 40 day3
3 30 day2
how can I do this without a cursor?
Thanks for your help.on SQL 2005,
select id, param, updateDate
from table2
where row_number() over(partition by id order by updateDate desc) = 1
*untested*|||Try this, if you don't have SQL Server 2005:
select Table2.id, Table2.param, Table2.updateDate
from Table2
join Table1 on Table1.id = Table2.id
where updateDate = (
select max(updateDate) from Table2 as T_copy
where T_copy = Table2.id
)
or
select id, param, updateDate
from Table2
where updateDate = (
select max(updateDate) from Table2 as T_copy
where T_copy = Table2.id
) and exists (
select * from Table1
where Table1.id = Table2.id
)
This can yield multiple rows per id if (id, updateDate) is not unique
and there are two or more updates for an id on most recent update date.
For SQL Server 2005, Alexander has the right idea, but windowed
functions cannot appear in the WHERE clause, and you'll have to do this:
select id, param, updateDate
from (
select
id, param, updateDate,
row_number() over (partition by id order by updateDate desc) as rn
from Table2
) as T
where rn = 1
Steve Kass
Drew University
tech77 wrote:

>Hi all,
>I am trying to do the following and am getting stuck.Your help wil be higly
>appreciated.
>I am matching two tables.If i get a single matching row then get that singl
e
>row
> but if i get >1 matching rows i should select only the one with the latest
>date.
>for example
>Table1
>--
>id value
>1 10
>2 20
>3 30
>Table 2
>--
>id param updateDate
>1 10 day1
>1 20 day2
>1 25 day3
>2 20 day2
>2 40 day 3
>3 30 day 2
>(day1 <day2<day3 etc)
>So i should get results as
>Result
>--
>1 25 day3
>2 40 day3
>3 30 day2
>how can I do this without a cursor?
>Thanks for your help.
>|||Here's one method, assuming that each id will only have one row per
day. If you have multiple rows per id per day, you have to have some
rule to determine which row you want returned.
Stu
DECLARE @.Table1 TABLE (id int, value int)
INSERT INTO @.TABLE1
SELECT 1, 10
UNION ALL
SELECT 2, 20
UNION ALL
SELECT 3, 30
DECLARE @.Table2 TABLE (id int, param int, UpdateDate smalldatetime)
INSERT INTO @.TABLE2
SELECT 1, 10, '20060301'
UNION ALL
SELECT 1, 20, '20060302'
UNION ALL
SELECT 1, 25, '20060303'
UNION ALL
SELECT 2, 20, '20060302'
UNION ALL
SELECT 2, 40, '20060303'
UNION ALL
SELECT 3, 30, '20060302'
SELECT t2.id, t2.param, t2.UpdateDate
FROM @.table1 t1 JOIN @.Table2 t2 ON t1.id=t2.id
JOIN (SELECT id, MAX(updateDate) AS upDateDate
FROM @.Table2 t2
GROUP BY id) t2_2 ON t2.ID = t2_2.ID
AND t2.UpDateDate = t2_2.UpdateDate