help to rewrite ugly query

Question

Post reply

help to rewrite ugly query

onixsoft

Mr or Mrs. 500

Points: 544
More actions
July 11, 2012 at 9:47 am

#264194

Hello experts,
Please help me to rewrite this ugly query
Its work properly but looks like overweighten
ok here is sample data
create table #tmp1 (
formfileid int,
identifier varchar(100),
templateid int,
[status] tinyint,
createdate datetime
)
insert into #tmp1
select 1, 'xx1', 1, 0, '1/1/2010'
union select 2, 'xx1', 1, 0, '1/2/2010'
union select 3, 'xx1', 1, 0, '1/3/2010'
union select 6, 'xx1', 2, 0, '1/2/2010'
union select 7, 'xx1', 2, 0, '1/3/2010'
union select 10, 'xx1', 3, 0, '1/3/2010'
Here is actually query I want to re-write
Purpose of the query is to update table to have Status = 1 for last record in the group by identifier and templateid ,
but keep Status = 0 for groups where identifier and templateid only single value in the group.
; with cte as (
select row_number() over (partition by identifier, templateid order by CreateDate desc ) as num, * from #tmp1
) ,
cte2 as (
select * from cte
where num > 1
) ,
cte3 as (
select identifier, templateid, max(num) as maxnum from cte group by identifier, templateid having max(num) = 1
)
update #tmp1 set [Status] = 1 where FormFileID in (select formfileid from cte where num = 1) and FormFileID not in (select formfileid from cte2)
and FormFileID not in (select formfileid from cte where identifier in (select identifier from cte3) and templateid in (select templateid from cte3))
Remove garbage.
select * from #tmp1
drop table #tmp1
Thanks,
Alex.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

with cte as (

select row_number() over (partition by identifier, templateid order by CreateDate desc ) as num,

count(*) over (partition by identifier, templateid) as totalnum,

*

from #tmp1)

update cte

set Status=1

where num=1 and totalnum>1;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Here is one way:

create table #tmp1 (

formfileid int,

identifier varchar(100),

templateid int,

[status] tinyint,

createdate datetime

);

insert into #tmp1

select 1, 'xx1', 1, 0, '1/1/2010'

union select 2, 'xx1', 1, 0, '1/2/2010'

union select 3, 'xx1', 1, 0, '1/3/2010'

union select 6, 'xx1', 2, 0, '1/2/2010'

union select 7, 'xx1', 2, 0, '1/3/2010'

union select 10, 'xx1', 3, 0, '1/3/2010';

go

SELECT * FROM #tmp1;

WITH DataChange AS (

SELECT

identifier,

templateid,

MAX(createdate) as createdate

FROM

#tmp1

GROUP BY

identifier,

templateid

HAVING

COUNT(*) > 1)

UPDATE t SET

[status] = 1

FROM

#tmp1 t

INNER JOIN DataChange dc

ON (t.identifier = dc.identifier

AND t.templateid = dc.templateid

AND t.createdate = dc.createdate);

SELECT * FROM #tmp1;

go

DROP TABLE #tmp1;

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Mark-101232 (7/11/2012)
with cte as ( select row_number() over (partition by identifier, templateid order by CreateDate desc ) as num, count(*) over (partition by identifier, templateid) as totalnum, * from #tmp1) update cte set Status=1
where num=1 and totalnum>1;

Forgot about doing it this way.

onixsoft Mr or Mrs. 500 Points: 544 More actions · Answer 4

Thanks guys,

Solution with "count(*) over" very nice

Alex.