CODEWITHSUNDEEP
sqlsql-serversql-server-2008
palak mehta
palak mehta

Reputation: 706

Delete duplicate rows from a table on the basis of column values

I have a table say PromoDescription:

--------------------------------------------------
| PromoId | Value | PromoType  |NightType|
--------------------------------------------------
|   101   |   11  |      a     |     b   |       
|   102   |   12  |      a     |     b   |       
|   103   |   17  |      c     |     d   |       
|   104   |   14  |      c     |     d   |

Above table has 4 columns and I have added the sample values.

Problem: For the same combination of PromotionType and NightType, I have to keep the highest value of discount and delete the rest of rows.

For sample values, row 1 and 4 should be deleted.

Upvotes: 0

Views: 563

Answers (4)

whytheq
whytheq

Reputation: 35557

I like to use NOT EXISTS but this is just a variation on the general theme:

select *
from yourtable a
where not exists
  (
  select 1
  from yourtable b
  where 
      a.PromoType = b.PromoType and
      a.NightType = b.NightType and
      a.Value < b.Value

  )

SQL FIDDLE HERE

Upvotes: 0

Himanshu
Himanshu

Reputation: 32602

You can also use join like this:

DELETE table1 
  FROM table1
  LEFT JOIN 
  (SELECT MAX(Value) as MaxValue, Promotype, nighttype FROM table1
   GROUP BY Promotype, nighttype
  )A
  ON table1.value = A.MaxValue
  AND table1.Promotype = A.Promotype
  AND table1.nighttype = A.nighttype
  WHERE A.MaxValue IS NULL;

See this SQLFiddle

Result

| PROMOID | VALUE | PROMOTYPE | NIGHTTYPE |
-------------------------------------------
|     102 |    12 |         a |         b |
|     103 |    17 |         c |         d |

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please check:

with c as
(
    select *, row_number() over(partition by PromotionType, NightType order by [Value] desc) as n
    from PromoDescription
)
delete from c
where n > 1;

Upvotes: 1

Taryn
Taryn

Reputation: 247620

You can use CTE to perform this:

;with cte as
(
  select promoid, value, promotype, NightType,
    row_number() over(partition by promotype, NightType order by value desc) rn
  from yourtable
)
delete
from cte
where rn > 1;

See SQL Fiddle with Demo

This will delete from the table anything that does not have the max value:

| PROMOID | VALUE | PROMOTYPE | NIGHTTYPE |
-------------------------------------------
|     102 |    12 |         a |         b |
|     103 |    17 |         c |         d |

Upvotes: 1

Related Questions