CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
ihorko
ihorko

Reputation: 6945

SQL Server query to take last grouped row by condition

I have simple SQL Server query:

declare @table table (id int, name nvarchar(5), deleted bit)

insert into @table(id, name, deleted) values(1, 'A1', 0)
insert into @table(id, name, deleted) values(2, 'A1', 0)
insert into @table(id, name, deleted) values(3, 'A1', 0)
insert into @table(id, name, deleted) values(4, 'A1', 1)
insert into @table(id, name, deleted) values(5, 'A2', 0)
insert into @table(id, name, deleted) values(6, 'A2', 0)

select
    max(id) as id,
    name
from @table
where deleted = 0
group by name

it returns to rows 

id  |name
--------------
3   |A1
6   |A2

but should return only one 

id  |name
-------------- 
6   |A2

since last (or max) id of A1 is deleted.

How to fix my query.

Thanks a lot.

Upvotes: 0

Views: 211

Answers (2)

Michael Buen
Michael Buen

Reputation: 39393

Another approach: http://www.sqlfiddle.com/#!3/697d7/5

create table t(id int, name nvarchar(5), deleted bit)

insert into t(id, name, deleted) values(1, 'A1', 0)
insert into t(id, name, deleted) values(2, 'A1', 0)
insert into t(id, name, deleted) values(3, 'A1', 0)
insert into t(id, name, deleted) values(4, 'A1', 1)
insert into t(id, name, deleted) values(5, 'A2', 0)
insert into t(id, name, deleted) values(6, 'A2', 0)


with no_deletions as
(
  select name
  from t
  group by name
  having max(nullif(cast(deleted as int),0)) is null
)
select    
    max(id) as id,
    name    
from t
where name in (select name from no_deletions)
group by name

Output:

| ID | NAME |
-------------
|  6 |   A2 |

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Since you could have a third set like this:

insert into @table(id, name, deleted) values(7, 'A3', 1)
insert into @table(id, name, deleted) values(8, 'A3', 0)

I assume you want the following returned as well, since the highest id for that name was not marked as deleted:

id    name
----  ----
8     A3

Then this query should do it:

;WITH x AS 
(
  SELECT id, name, deleted, 
    rn = ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC)
  FROM @table
)
SELECT id = MAX(id), name 
FROM x
WHERE NOT EXISTS
(
  SELECT 1 FROM x AS x2 
  WHERE name = x.name 
  AND deleted = 1 AND rn = 1
)
GROUP BY name;

If you only want rows returned where no row for a particular name was ever deleted, then it's slightly simpler:

SELECT id = MAX(id), name
FROM @table AS t
WHERE NOT EXISTS 
(
  SELECT 1 FROM @table 
  WHERE name = t.name AND deleted = 1
)
GROUP BY name;

Upvotes: 3

Related Questions