And how do you get a fast count when having the following table

TABLE (
X INT NOT NULL,
Y CHAR NOT NULL,
PRIMARY KEY (X,Y)
)
and want to know the number of records that satisfies for Y=Y1 and Y=Y2?

eg.

X Y
------------
X1 Y1 <-----
X2 Y1
X1 Y2 <-----
X2 Y3
X3 Y1 <-----
X3 Y2 <-----

The result here would be count(*) =2

The way I count is:

SELECT COUNT(*)
FROM TABLE T
WHERE T.Y = Y1
AND T.X IN (SELECT T.X FROM T WHERE T.Y = Y2)

whereby having Y1 the value with the lowest count (maintained in a table
like you suggested).
Is there also a faster way for this scenario?

Dennis


Nigel Weeks wrote:

> I never saw that method when it first came around, but man it's nice.
> That's another solution for the 'Handy Bits' folder!
>
> N