Truncate and RowCount/Number - 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 2012 Forums

Transact-SQL (2012)

Truncate and RowCount/Number

Author

Topic

MichelleMabbs
Starting Member

24 Posts

Posted - 2015-03-03 : 11:58:34

hi I would like have a query that uses a row_number window function to number rows and then delete rows where the row number is > 3

the data I have looks like this and the window function needs to be

Row_Number()
OVER (Partition by Primarykey Order by LastOnDuty Desc ) as RowNo

This is so I get the latest time on duty by primary key

Primarykey LastOnDuty
CA104-27/02/2015-08:00:00 27/02/2015 16:30
CA104-27/02/2015-20:00:00 27/02/2015 23:07
CA126-27/02/2015-07:00:00 27/02/2015 17:36
CA171-27/02/2015-16:00:00 27/02/2015 23:28
CA174-27/02/2015-07:00:00 27/02/2015 19:14
CA174-27/02/2015-19:00:00 27/02/2015 04:07
CA262-27/02/2015-06:30:00 27/02/2015 17:45
CA262-27/02/2015-18:30:00 27/02/2015 23:01
CA285-27/02/2015-19:00:00 27/02/2015 23:07
CA332-27/02/2015-18:30:00 27/02/2015 23:51
CA361-27/02/2015-07:00:00 27/02/2015 17:46
CA412-27/02/2015-07:00:00 27/02/2015 19:03
CA412-27/02/2015-19:00:00 27/02/2015 22:58
CA461-27/02/2015-07:00:00 27/02/2015 19:16
CR296-27/02/2015-08:00:00 27/02/2015 15:31
CR316-27/02/2015-07:00:00 27/02/2015 19:19
CR317-27/02/2015-07:00:00 27/02/2015 19:58
CR317-27/02/2015-19:00:00 27/02/2015 01:53
CR427-27/02/2015-08:00:00 27/02/2015 19:47
CR428-27/02/2015-18:00:00 27/02/2015 18:44
CR505-27/02/2015-17:30:00 27/02/2015 22:55
NA217-27/02/2015-20:00:00 27/02/2015 06:51
NA235-27/02/2015-20:00:00 27/02/2015 23:42
NA236-27/02/2015-10:00:00 27/02/2015 21:07
NA240-27/02/2015-19:00:00 27/02/2015 23:52
NA251-27/02/2015-07:00:00 27/02/2015 19:52
NA261-27/02/2015-20:00:00 27/02/2015 23:15
NA292-27/02/2015-07:00:00 27/02/2015 16:38
NA302-27/02/2015-19:30:00 27/02/2015 23:52
NA322-27/02/2015-12:00:00 27/02/2015 21:43

I would be grateful for your assistance thanks

Michelle

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-03 : 12:32:51

maybe something like this:



declare @t table (Primarykey varchar(40),	LastOnDuty varchar(40))
insert into @t (Primarykey, LastOnDuty) values

('CA104','27/02/2015-08:00:00 27/02/2015 16:30'),
('CA104','27/02/2015-20:00:00 27/02/2015 23:07'),
('CA126','27/02/2015-07:00:00 27/02/2015 17:36'),
('CA171','27/02/2015-16:00:00 27/02/2015 23:28'),
('CA174','27/02/2015-07:00:00 27/02/2015 19:14'),
('CA174','27/02/2015-19:00:00 27/02/2015 04:07'),
('CA262','27/02/2015-06:30:00 27/02/2015 17:45'),
('CA262','27/02/2015-18:30:00 27/02/2015 23:01'),
('CA285','27/02/2015-19:00:00 27/02/2015 23:07'),
('CA332','27/02/2015-18:30:00 27/02/2015 23:51'),
('CA361','27/02/2015-07:00:00 27/02/2015 17:46'),
('CA412','27/02/2015-07:00:00 27/02/2015 19:03'),
('CA412','27/02/2015-19:00:00 27/02/2015 22:58'),
('CA461','27/02/2015-07:00:00 27/02/2015 19:16'),
('CR296','27/02/2015-08:00:00 27/02/2015 15:31'),
('CR316','27/02/2015-07:00:00 27/02/2015 19:19'),
('CR317','27/02/2015-07:00:00 27/02/2015 19:58'),
('CR317','27/02/2015-19:00:00 27/02/2015 01:53'),
('CR427','27/02/2015-08:00:00 27/02/2015 19:47'),
('CR428','27/02/2015-18:00:00 27/02/2015 18:44'),
('CR505','27/02/2015-17:30:00 27/02/2015 22:55'),
('NA217','27/02/2015-20:00:00 27/02/2015 06:51'),
('NA235','27/02/2015-20:00:00 27/02/2015 23:42'),
('NA236','27/02/2015-10:00:00 27/02/2015 21:07'),
('NA240','27/02/2015-19:00:00 27/02/2015 23:52'),
('NA251','27/02/2015-07:00:00 27/02/2015 19:52'),
('NA261','27/02/2015-20:00:00 27/02/2015 23:15'),
('NA292','27/02/2015-07:00:00 27/02/2015 16:38'),
('NA302','27/02/2015-19:30:00 27/02/2015 23:52'),
('NA322','27/02/2015-12:00:00 27/02/2015 21:43')

delete from t
from @t t
cross apply (
	select Row_Number()
		OVER (Partition by Primarykey Order by LastOnDuty Desc ) as RowNo 
	from @t t1
	where t.Primarykey = t1.Primarykey
) subq
where subq.RowNo > 1

select * from @t
order by Primarykey

MichelleMabbs
Starting Member

24 Posts

Posted - 2015-03-05 : 03:59:39

Hi thank you that is great :)

Michelle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources