DELETE With group by

Question

Table with the following

CREATE TABLE [dbo].[Poll](
    [PollID] [bigint] IDENTITY(1,1) NOT NULL,
    [LoginID] [bigint] NOT NULL,
    [FacilityID] [bigint] NOT NULL,
    [PolledAt] [datetime] NOT NULL,
)

I have to empty this table out nightly...but leave the maximum row based on the PolledAt field groupped by the LoginID field. Meaning a user (LoginID) has multiple rows, by the end of the night that user should only have one row after the delete. That row should be the MAX(PolledAt) value.

I can get the records I dont want to delete with this:

SELECT
    LoginID,
    MAX(PolledAt) AS MaxPolledAt
FROM
    Poll
GROUP BY
    LoginID

But then I dont know how to form my delete because I am not displaying the primary key which is the pollid. I need to take the result of the above select and delete everything except what that select returns.

Answer 1

You can use a Common Table Expression and the row_number() function to figure out what rows need to be deleted.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE [dbo].[Poll](
    [PollID] [bigint] IDENTITY(1,1) NOT NULL,
    [LoginID] [bigint] NOT NULL,
    [FacilityID] [bigint] NOT NULL,
    [PolledAt] [datetime] NOT NULL,
);


insert into Poll values(1, 1, getdate());
insert into Poll values(1, 1, getdate()+1);
insert into Poll values(1, 1, getdate()+2);
insert into Poll values(1, 1, getdate()+3);
insert into Poll values(2, 2, getdate()+4);

Query 1:

with C as
(
  select row_number() over(partition by LoginID order by PolledAt desc) as rn
  from Poll
)
delete from C
where rn > 1;

Results:

Query 2:

select *
from Poll;

Results:

| POLLID | LOGINID | FACILITYID |                        POLLEDAT |
-------------------------------------------------------------------
|      4 |       1 |          1 | February, 08 2013 21:48:34+0000 |
|      5 |       2 |          2 | February, 09 2013 21:48:34+0000 |