Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Get this group but not that group

Wow! This seems to be blog post number 100.

Today I stumbled across an interesting problem where OP had a set of records. Every record were "typed" and "grouped".
Now OP wanted to get distinct group id's for those groups having at least one record typed 33, but not having a record typed 11.

This can be done in a number of ways, but I wanted to write an efficiently running query.

DECLARE     @Sample TABLE
            (
                        MeetingID INT,
                         TicketID INT
            )
 
INSERT      @Sample
SELECT      1, 11 UNION ALL
SELECT      1, 22 UNION ALL
SELECT      1, 33 UNION ALL
SELECT      2, 22 UNION ALL
SELECT      2, 33 UNION ALL
SELECT      3, 11 UNION ALL
SELECT      3, 33 UNION ALL
SELECT      5, 22 UNION ALL
SELECT      4, 33
 
SELECT      MeetingID
FROM        @Sample
GROUP BY    MeetingID
HAVING      MAX(CASE TicketID WHEN 11 THEN 2 WHEN 33 THEN 1 ELSE 0 END) = 1

Print | posted on Friday, May 29, 2009 10:57 AM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET