delete all apart first first record of episode?

• Edward-445599

  Hall of Fame

  Points: 3147

  More actions

  July 23, 2018 at 3:07 am

  #373791

  I have a table full of episodes. So for one episode there might be 10 records. I want to delete all records apart from the original. Current I have a temp table where I have extracted the records I want to delete. Then I use the primary key.
  But I was wondering if there is an alternative?

  Example
  Turn this
  Primarykey, record , episode
  1,1,1
  2,2,1
  3,3,1
  4,1,2
  5,2,2
  6,3,2
  7,1,3
  8,2,3
  9,3,3
  To this

  1,1,1
  4,1,2
  7,1,3

    

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  July 23, 2018 at 3:35 am

  #1998558

  Delete table
  Where record <> 1
  Looks like it would do it.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Edward-445599

  Hall of Fame

  Points: 3147

  More actions

  July 23, 2018 at 5:57 am

  #1998570

  Phil Parkin - Monday, July 23, 2018 3:35 AM

  Delete table
  Where record <> 1
  Looks like it would do it.

  ahh yes , missed some details unfortunately the start number inst always '1' but the sequence is maintained what ever number you start at

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  July 23, 2018 at 6:33 am

  #1998574

  Edward-445599 - Monday, July 23, 2018 3:07 AM

  I have a table full of episodes. So for one episode there might be 10 records. I want to delete all records apart from the original. Current I have a temp table where I have extracted the records I want to delete. Then I use the primary key.
  But I was wondering if there is an alternative?

  Example
  Turn this
  Primarykey, record , episode
  1,1,1
  2,2,1
  3,3,1
  4,1,2
  5,2,2
  6,3,2
  7,1,3
  8,2,3
  9,3,3
  To this

  1,1,1
  4,1,2
  7,1,3

    
How about this?

  create table seq
(
Primarykey int, record int , episode int)

  insert into seq values(1,1,1);
insert into seq values(2,2,1);
insert into seq values(3,3,1);
insert into seq values(4,1,2);
insert into seq values(5,2,2);
insert into seq values(6,3,2);
insert into seq values(7,1,3);
insert into seq values(8,2,3);
insert into seq values(9,3,3);

  select Primarykey,record,episode from
(
select *,
row_number ()over(partition by episode order by record) rnk from seq
)sequence
where rnk=1

  Primarykey	record	episode

  1	1	1
  4	1	2
  7	1	3

  3 rows (showing 1 to 3)
 

  Saravanan

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  July 23, 2018 at 6:54 am

  #1998579

  Here is the DELETE version of the query posted above:

  DROP TABLE IF EXISTS #seq;

  CREATE TABLE #seq
(
  Primarykey INT NOT NULL PRIMARY KEY CLUSTERED,
  record INT NOT NULL,
  episode INT NOT NULL
);

  INSERT #seq
(
  Primarykey,
  record,
  episode
)
VALUES
(1, 1, 1),
(2, 2, 1),
(3, 3, 1),
(4, 1, 2),
(5, 2, 2),
(6, 3, 2),
(7, 1, 3),
(8, 2, 3),
(9, 3, 3);

  WITH ordered
AS (SELECT rnk = ROW_NUMBER() OVER (PARTITION BY s.episode ORDER BY s.record)
  FROM #seq s)
DELETE o
FROM ordered o
WHERE o.rnk > 1;

  SELECT *
FROM #seq s;


  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  July 23, 2018 at 7:01 am

  #1998580

  Phil Parkin - Monday, July 23, 2018 6:54 AM

  Here is the DELETE version of the query posted above:

  DROP TABLE IF EXISTS #seq;

  CREATE TABLE #seq
(
  Primarykey INT NOT NULL PRIMARY KEY CLUSTERED,
  record INT NOT NULL,
  episode INT NOT NULL
);

  INSERT #seq
(
  Primarykey,
  record,
  episode
)
VALUES
(1, 1, 1),
(2, 2, 1),
(3, 3, 1),
(4, 1, 2),
(5, 2, 2),
(6, 3, 2),
(7, 1, 3),
(8, 2, 3),
(9, 3, 3);

  WITH ordered
AS (SELECT s.Primarykey,
    rnk = ROW_NUMBER() OVER (PARTITION BY s.episode ORDER BY s.record)
  FROM #seq s)
DELETE s
FROM #seq s
  JOIN ordered o
   ON s.Primarykey = o.Primarykey
WHERE o.rnk > 1;

  SELECT *
FROM #seq s;

  I didn't read the problem statements .I just saw the expected output. Good one

  Saravanan

• Edward-445599

  Hall of Fame

  Points: 3147

  More actions

  July 24, 2018 at 7:26 am

  #1998774

  wow ace thank you so much a very neat solution, I do not suppose, you can point me in the direct of a tutorial which explains whats going on here?

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  July 24, 2018 at 8:48 am

  #1998797

  Edward-445599 - Tuesday, July 24, 2018 7:26 AM

  wow ace thank you so much a very neat solution, I do not suppose, you can point me in the direct of a tutorial which explains whats going on here?

  There are a couple of things to get to grips with.

  • Most importantly, Window Functions, which allow you to order resultsets any way you want and to return a number ('rnk' in the above) which shows this ordering (and can thus be used to filter the returned results). Check out this link.
  • Knowing that you can delete from a CTE and that this deletion will cascade up to the table from which the CTE is derived is a handy shortcut.

  Post back if you'd like any further explanation.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin