complex delete - 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

complex delete

Author

Topic

pentser
Starting Member

2 Posts

Posted - 2011-09-21 : 04:04:32

hi
please help me to write sql s.

i have table - LOCKS

name | session | lock_row | date

raf | 01 | | 21/09/2011 09:50:22
raf | 01 | | 21/09/2011 10:10:00
el | 07 | | 21/09/2011 10:10:00
dan | 05 | | 21/09/2011 11:10:24
raf | 01 | | 21/09/2011 10:30:24
dan | 05 | | 21/09/2011 10:10:00
raf | 02 | | 21/06/2011 10:10:00

i want to write sql s. that delete rows:
if some name at the same session have more than 1 row at the table
the row with the last date/time stay and the athers will delete

at that table the rows 1,2 and 6 will delete

thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-09-21 : 05:32:32

[code]
declare @LOCKS table ( name varchar(3), session char(2), lock_row varchar(2), date datetime)

insert into @LOCKS
select 'raf' , '01' , '' , '2011-09-21 09:50:22'
union select 'raf' , '01' , '' , '2011-09-21 10:10:00'
union select 'el' , '07' , '' , '2011-09-21 10:10:00'
union select 'dan' , '05' , '' , '2011-09-21 11:10:24'
union select 'raf' , '01' , '' , '2011-09-21 10:30:24'
union select 'dan' , '05' , '' , '2011-09-21 10:10:00'
union select 'raf' , '02' , '' , '2011-06-21 10:10:00'

DELETE
LOCKS
FROM
@LOCKS LOCKS
INNER JOIN
(
SELECT
ROW_NUMBER() OVER (PARTITION BY name, session ORDER BY name, session, date DESC) rownum
, name
, session
, date
FROM
@LOCKS
) rowstodelete
ON
LOCKS.name = rowstodelete.name
AND
LOCKS.session = rowstodelete.session
AND
LOCKS.date = rowstodelete.date
WHERE
rownum > 1

SELECT * FROM @LOCKS
[/code]

pentser
Starting Member

2 Posts

Posted - 2011-09-22 : 09:09:06

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources