One more stupid question... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

One more stupid question...

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-06 : 10:41:24

Here's the data:

declare @act table (id int, cat varchar(2), dt datetime)
insert	@act
select	1, 'A', '11/02/2006' union all
select	2, 'B', '12/03/2005' union all
select	2, 'C', '12/02/2005' union all
select	2, 'D', '12/04/2005' union all
select	3, 'E', '10/09/2006'


declare @inst table (id int, other_id varchar(4), type varchar(2))
insert	@inst
select	1, '4802', 'M' union all
select	2, '4803', 'M' union all
select	2, '4803', 'B' union all
select	2, '4803', 'F' union all
select	3, '4804', 'M'

I need to retrieve records from table act.
For each ID I only need most recent record.
Instead of ID, I need to show other_id from table inst.
I have a list of other_id I need to show records for (e.g. WHERE other_id IN ('4802', '4803')).

For the data above I'd expect:

other_id  cat  dt
'4802'    'A'  '11/02/2006'
'4803'    'D'  '12/04/2005'

How do I write such query?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 10:52:17

[code]SELECT distinct i.other_id,
a.cat,
a.dt
FROM @inst AS i
INNER JOIN @act AS a ON a.id = i.id
WHERE a.dt = (select max(x.dt) from @act as x where x.id = a.id)[/code]

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 10:52:36

[code]
declare @act table (id int, cat varchar(2), dt datetime)
insert @act
select 1, 'A', '11/02/2006' union all
select 2, 'B', '12/03/2005' union all
select 2, 'C', '12/02/2005' union all
select 2, 'D', '12/04/2005' union all
select 3, 'E', '10/09/2006'

declare @inst table (id int, other_id varchar(4), type varchar(2))
insert @inst
select 1, '4802', 'M' union all
select 2, '4803', 'M' union all
select 2, '4803', 'B' union all
select 2, '4803', 'F' union all
select 3, '4804', 'M'

select distinct i.other_id, a.cat, a.dt
from @act a inner join @inst i
on a.id = i.id
where a.dt in (select max(x.dt) from @act x inner join @inst y
on x.id = y.id
where y.other_id = i.other_id
and y.other_id in ('4802', '4803')
)
and i.other_id in ('4802', '4803')
order by i.other_id

[/code]

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-06 : 20:06:24

Thanks so much to both of you.

It depresses me though to see you come up with an answer in 5 minutes - I only asked after trying to write it for half an hour...

Anyway - thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources