2

I have a table with the below structure. I would like to delete all of the records that records that match ColA for any of the ColB's that have a ColC that has an even-numbered value. But only for the first instance of ColA. (In this example all of the AA records would be deleted)

ColA and ColB are unique and sorted in alphabetical order. ORderByCol is part of the query that builds this table: ROW_NUMBER() OVER(PARTITION BY ColA ORDER BY ColB)

ColA    ColB    ColC    OrderByCol
AA      A       8       1
AA      B       3       2
AA      F       5       3
BB      B       7       1
BB      D       9       2
CC      A       1       1
CC      Q       5       2

I can do this with an ColA in a subquery, but that seems like a lot of work and it is really slow.

Query:

DELETE T FROM Table1 AS T
WHERE T.ColA IN (
    SELECT DISTINCT ColA FROM Table1
    WHERE EXISTS (SELECT * FROM Table1 WHERE ColC % 2)
    AND ORderByCol = 1
)

So the query will give me a list of all unique ColA values that have the first item not primary, but it is really slow. I'm sure there is a faster way to do this.

EDIT: If someone knows a better way to do what I am asking that would be awesome! I always like learning new ways to do things in SQL. However I found out why my query was slow. I didn't have an index on the table because I thought that table variables (@table1) tables couldn't have indexes. Once I put in an index on my table it reduced my run time from 1 min 20 sec to 2 sec.

Thank you to every one who has helped out with this.

2
  • 5
    Your explanation for a selection criteria is a bit confusing. Can you include the query you've run, specifying what it is doing wrong? – Ezequiel Tolnay Apr 7 '16 at 0:33
  • 2
    "ColA and B are ... sorted in alphabetical order" - there is no inherent order in a table's data. SQL Server is free to access the rows, and return them to the application, in an sequence it sees fit. Did you mean ColA and ColB have unique, ascending indexes defined? – Michael Green Apr 7 '16 at 5:44
3

Your query is more complicated that it needs to be. DISTINCT is redundant there and the internal subquery is not needed. You could rewrite as:

DELETE T FROM Table1 AS T
WHERE T.ColA IN (
    SELECT ColA 
    FROM Table1
    WHERE ColC % 2 = 0 
      AND OrderByCol = 1
) ;
0
4

If this was SQL Server 2012 or later version, you could use the FIRST_VALUE window function:

WITH cte AS
  (
    SELECT
      FirstColC = FIRST_VALUE(ColC) OVER (PARTITION BY ColA ORDER BY ColB ASC)
    FROM
      dbo.Table1
  )
DELETE FROM
  cte
WHERE
  FirstColC % 2 = 0
;

In SQL Server 2005 FIRST_VALUE can be replaced with a correlated TOP (1) + ORDER BY subquery:

WITH cte AS
  (
    SELECT
      FirstColC = (
        SELECT TOP (1)
          ColC
        FROM
          dbo.Table1 AS toprow
        WHERE
          toprow.ColA = main.ColA
        ORDER BY
          ColB ASC
      )
    FROM
      dbo.Table1 AS main
  )
DELETE FROM
  cte
WHERE
  FirstColC % 2 = 0
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.