max date record - 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)

max date record

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2008-03-05 : 13:36:20

I do have following data. I need to get lastest record for each secid.

Id SecId EFFDt SecName
1 100 01/01/1990 Math
1 100 01/01/1991 Math
1 200 01/01/1990 Phys
1 200 01/01/1991 Phys

Output should be:
Id SecId EFFDt SecName
1 100 01/01/1991 Math
1 200 01/01/1991 Phys

Thanks...

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-05 : 14:07:27

declare @a table (
Id int,
SecId int,
EFFDt datetime,
SecName varchar(20)
)
insert into @a
select 1, 100, '01/01/1990', 'Math' union
select 1, 100, '01/01/1991', 'Math' union
select 1, 200, '01/01/1990', 'Phys' union
select 1, 200, '01/01/1991', 'Phys'

-- select * from @a

select id, SecID, max(EFFDt) as EFFDt, Max(SecName) as SecName
from @a
group by id, SecID

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

cognos79
Posting Yak Master

241 Posts

Posted - 2008-03-05 : 14:23:02

slight modification...the secname can be modified when users update the record. so I need to gethold of the record that has the latest eff date...we cant use max(secname) at tht time...run the following query.

declare @a table (
Id int,
SecId int,
EFFDt datetime,
SecName varchar(20)
)
insert into @a
select 1, 100, '01/01/1990', 'Mathasdfasdfasdf' union
select 1, 100, '01/01/1991', 'Mat' union
select 1, 100, '01/01/2001', 'M' union
select 1, 100, '01/01/1991', 'Mat' union
select 1, 200, '01/01/1990', 'Phys' union
select 1, 200, '01/01/1991', 'Phys'

select * from @a

select Id, SecId, max(EFFDt) as EFFDt, Max(SecName) as SecName
from @a
group by Id, SecId

the resultset should contain this record
1, 100, '01/01/2001', 'M'

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-05 : 14:35:26

[code]
declare @a table (
Id int,
SecId int,
EFFDt datetime,
SecName varchar(20)
)
insert into @a
select 1, 100, '01/01/1990', 'Mathasdfasdfasdf' union
select 1, 100, '01/01/1991', 'Mat' union
select 1, 100, '01/01/2001', 'M' union
select 1, 100, '01/01/1991', 'Mat' union
select 1, 200, '01/01/1990', 'Phys' union
select 1, 200, '01/01/1991', 'Phys'

select Id, SecId, max(EFFDt) as EFFDt, (select SecName from @a as X where ID = a.id and SecID = a.SECID and EFFDT = (select Max(EFFDT) from @a where ID = a.id and SecID = a.SECID )) as SecName
from @a as a
group by Id, SecId

[/code]

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources