Compound Primary Key SQL Trickery Using Exists Keyword!

I recently came across an interesting problem. What I was trying to do was to figure out which id’s were new to the quarter 201001. Take a look at the following queries.

select id
from table as a
group by id, quarter_index
having count(*) = 1 and quarter_index = 201001

select id
from table as a
group by id
having count(*) = 1 and exists (
select quarter_index
from table as b
where b.id = a.id and b.quarter_index = 201001)

Table looks like this:

ID Quarter
1 201001
1 200901
3 200901
4 201001

The first query produces ID’s 1 and 4. The second produces the ID 4. The difference between the two queries are as follows: query 1 is including both the quarter_index and the id in the group by statement thereby including row 1 from the table; query 2 is much different, it is only using id in the group by and is using the exists word in SQL. In query 2, the first part of the query is returning ID’s that only “exist” in one quarter. The second part of the query is saying “of this group of ID’s, give me only the ones that exist in quarter 201001”.

This is something that is very interesting and efficient as well. This is the first time I have used the exists keyword in the having portion of a SQL statement. I think it is rather cool and hope this helps someone.

Advertisements