select stmnt using MAX() funtion for batch insert

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

Old Forums

CLOSED - General SQL Server

select stmnt using MAX() funtion for batch insert

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-10-29 : 08:24:59

Natalie writes "Hi,
I am trying to filter data from the source table for the batch insert.
Source table has a compound primary key: Id1 varchar (7), and Id2 (varchar 5). There is also a CREATED_DATE_TIME date field.
I need to insert in the destination table only records that have
latest CREATED_DATE_TIME stamp with unique Id1. For example,
if there are 2 records: 1000000 11111 7/24/2004 5:50:10 PM
1000000 22222 7/24/2004 5:50:16 PM

the second record has to be inserted in the destination table.

select Id1, MAX(CREATED_DATE_TIME)as MAX_TIME from tblSOURCE group by MAX_TIME returns that record.

With the limitations on aggregate function and group by rules, how do I select the rest of the fields from the SOURCE table for the insert operation?

Thank you
Natalie"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-29 : 08:46:50

[code]
Select *
From tblSOURCE A
Where Created_Date_Time = (select MAX(CREATED_DATE_TIME)as MAX_TIME from tblSOURCE Where id1 = A.id1)
[/code]

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources