Hi ! Dennis,

Dennis Mulder wrote:

>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
>
>
>

Don't know if I get it right.

but how about:

select
count(*)
from
Table T1 join
Table T2 on (T2.X1 = T1.X1)
where
T1.Y1 = 1 and
T2.Y1 = 2


see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.5.2 - Release Date: 28/02/2005