CODEWITHSUNDEEP
sql
scader
scader

Reputation: 525

Remove duplicate rows by value

I need to perform a group by, but only on rows that meet a condition, otherwise return all rows that do not meet the condition. For example, in the following table, I want to group only rows that have '1' in the "active" field, and return all rows that do not.

TABLE (id, label, active):

1, A, 1
2, A, 1
3, B, 0
4, B, 0

Would return:

1, A, 1
3, B, 0
4, B, 0

Upvotes: 2

Views: 891

Answers (3)

tvanfosson
tvanfosson

Reputation: 532465

You could use a CASE statement

select min(id) as id, label, active
from (
     select id, label, active,
           case
             when active = 1 then 'active'
             else convert(varchar,newid()) as discriminator
           end
     from table) t
group by label, active, discriminator

this will give a common value to all rows that meet the condition (active = 1) and a unique value to all other rows so that they remain ungrouped.

I don't have access to SQL server at the moment so this is completely untested.

Upvotes: 0

Jackson Miller
Jackson Miller

Reputation: 1510

Here is the simplest way I can think of. It is a case statement in the group by. If the condition is met then group by the label, otherwise group by the primary key.

SELECT id, label, active
FROM table
GROUP BY
  CASE
    WHEN active = 1 THEN active
    ELSE id END

If you want to group by active and label:

SELECT id, label, active
FROM table
GROUP BY
  CASE
    WHEN active = 1 THEN active
    ELSE id END,
  label

EDIT: I misunderstood which field you want to group on. Corrected now.

Upvotes: 3

Christoffer Lette
Christoffer Lette

Reputation: 14816

Perhaps the layout of the query could be something like this:

select
  min(id),
  label,
  max(active)
from
  Foo
where
  active = 1
group by
  label

union

select
  id,
  label,
  active
from
  Foo
where
  active = 0

Upvotes: 0

Related Questions