SQLTeam.com | Weblogs | Forums

Select single row from double rows with same id but different event type

Helllo I have the following table

ID_1 Status Event_Type Insert_Mode ID_2
1 V M G 100
2 V K I 100
3 V M G 101
4 V L I 103
5 V M G 104
6 V L I 107
7 V L I 110
8 V M G 120
9 V K I 120

In the same sql query

  1. I want to select only the elements with Event type M from all the elements with same ID_2 which can be located in maximum 2 rows

hope this helps

; with cte as 
(
SELECT ID_2 , COUNT(ID_2) FROM TABLE GROUP BY ID_2 HAVING COUNT(ID_2) = 2 
) 
SELECT  * FROM   ( select * from TABLE a join CTE b on a.id_2 = b.id_2 )
where Event_Type = 'M'

This may not be elegant but:
SELECT *
FROM TABLE
WHERE ID_2 IN (
SELECT ID_2
FROM TABLE
GROUP BY ID_2
HAVING COUNT(ID_2) = 2 )
AND Event_Type = 'M';