CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
kakridge
kakridge

Reputation: 2293

Complex Query To Group and (Potentially Union) Data

Suppose I have a SQL Server table like this:

Event |  Item1   |  Item2  | Status
-------------------------------------------
1        Parent1   Child1      1
2        Parent1   Child2      1
3        Parent2   Child1      1
4        Parent2   Child2      1
5        Parent3   Child1      1
6        Parent3   Child2      0
7        Parent4   Child1      1
8        Parent4   Child1      1
9        Parent4   Child2      0

I want a query that gives me the following:

  1. All rows with a status of 0. => That would be IDs 6 and 9.
  2. All rows that have Item2 = 'Child1', that have a status of 1, that have the same Item1 as the result in #1. => That would be IDs 5,7 and 8.
  3. There can be multiple rows that match #2 and the matches should be constrained to maximum id based per Item 1.

So the final result should look like this:

6, Parent3, Child2, 0
9, Parent4, Child2, 0
5, Parent3, Child1, 1
8, Parent4, Child1, 1

This solutions is in SQL 2008 R2. I'm open to temp tables, unions, joins, etc. I can't change the schema. Any help is extremely appreciated.

Upvotes: 1

Views: 76

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44326

This requires sqlserver 2005

;WITH a as
(
SELECT Event, Item1, Item2, Status,
min(status) over( partition by item1) lowstatus,
max(Event) over( partition by item2, status) hievent
FROM table
)
SELECT Event, Item1, Item2, Status
FROM a
WHERE status = 0
or Item2 = 'Child1' and Status = 1 and lowstatus = 0 and hievent = Event

Upvotes: 1

dwonisch
dwonisch

Reputation: 5785

This should work but I do not know if it is really optimized for large data.

CREATE TABLE #tab (id int, Item1 varchar(255), Item2 varchar(255), Status bit)

INSERT INTO #tab VALUES
(1,'Parent1','Child1', 1 ),
(2,'Parent1','Child2', 1 ),
(3,'Parent2','Child1', 1 ),
(4,'Parent2','Child2', 1 ),
(5,'Parent3','Child1', 1 ),
(6,'Parent3','Child2', 0 ),
(7,'Parent4','Child1', 1 ),
(8,'Parent4','Child1', 1 ),
(9,'Parent4','Child2', 0 )

SELECT * INTO #filter FROM #tab WHERE Status = 0

SELECT * INTO #results FROM #tab 
WHERE
 Id IN (
    SELECT MAX(ID) FROM #tab 
        WHERE Item2 = 'Child1' AND Status = 1 AND Item1 IN (SELECT Item1 FROM #filter) 
        GROUP BY Item1  
 )

SELECT * FROM #filter
UNION
SELECT * FROM #results

Upvotes: 1

Related Questions