Help with insert using max() - 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

General SQL Server Forums

New to SQL Server Programming

Help with insert using max()

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2011-04-11 : 08:12:18

I have a table (#table1) where I want to insert records in from another table #table2. The problem is that table2 contains several duplicate rows only differed by the column datumlop. I want to insert the row containing max(datumlop) grouped by fnr and dbnr.

create #table1(
fnr varchar(50)
,dbnr varchar(50)
,datumlop varchar(50)
,col1 int
,col2 int
,col3 varchar(50)
.
.
)

create #table2(
fnr varchar(50)
,dbnr varchar(50)
,datumlop varchar(50)
,col1 int
,col2 int
,col3 varchar(50)
)

insert into #table2(fnr, dbnr,datumlop,col1,col2,col3) values('123456', 99999, '20110112',1,1,'2')
insert into #table2(fnr, dbnr,datumlop,col1,col2,col3) values('123456', 99999, '20110101',9,2,'2')
insert into #table2(fnr, dbnr,datumlop,col1,col2,col3) values('123456', 99999, '20110121',6,2,'15')
insert into #table2(fnr, dbnr,datumlop,col1,col2,col3) values('456666', 19993, '20101231',6,3,'01')

Theese rows are going to be inserted!
'123456', 99999, '20110121',6,2,'15'
'456666', 19993, '20101231',6,3,'01'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-11 : 08:23:46

[code]--I have a table (#table1) where I want to insert records in from another table #table2.
--The problem is that table2 contains several duplicate rows only differed
--by the column datumlop.
--I want to insert the row containing max(datumlop) grouped by fnr and dbnr.

create table #table1(
fnr varchar(50)
,dbnr varchar(50)
,datumlop varchar(50)
,col1 int
,col2 int
,col3 varchar(50)
)

create table #table2(
fnr varchar(50)
,dbnr varchar(50)
,datumlop varchar(50)
,col1 int
,col2 int
,col3 varchar(50)
)

insert into #table2(fnr, dbnr,datumlop,col1,col2,col3) values('123456', 99999, '20110112',1,1,'2')
insert into #table2(fnr, dbnr,datumlop,col1,col2,col3) values('123456', 99999, '20110101',9,2,'2')
insert into #table2(fnr, dbnr,datumlop,col1,col2,col3) values('123456', 99999, '20110121',6,2,'15')
insert into #table2(fnr, dbnr,datumlop,col1,col2,col3) values('456666', 19993, '20101231',6,3,'01')

--Theese rows are going to be inserted!
--'123456', 99999, '20110121',6,2,'15'
--'456666', 19993, '20101231',6,3,'01'

insert #table1(fnr,dbnr,datumlop,col1,col2,col3)
select fnr,dbnr,datumlop,col1,col2,col3
from
(
select
row_number() over (partition by fnr,dbnr order by datumlop desc) as rownum,
*
from #table2
)dt
where rownum=1
[/code]
edit: typo

No, you're never too old to Yak'n'Roll if you're too young to die.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-11 : 08:41:09

select fnr,dbnr,datumlop,col1,col2,col3 into #table1
from
(
select Row=dense_rank() over (Partition by Fnr order by col3),* from #table2
) as t
where row=1

Raghu' S

maevr
Posting Yak Master

169 Posts

Posted - 2011-04-11 : 08:45:04

Thank you, it seems to work. I forgot to add that I have a column (TRTYP) that specifies what operation is to be done.

TRTYP should be 2 meaning that it is a row to be inserted.

Should I add this in the where clause?

insert #table1(fnr,dbnr,datumlop,col1,col2,col3)
select fnr,dbnr,datumlop,col1,col2,col3
from
(
select
row_number() over (partition by fnr,dbnr order by datumlop desc) as rownum,
*
from #table2 where trtyp = 2
)dt
where rownum=1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-11 : 08:47:40

Looks fine to me

Give it a try.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources