Hi Alexandre,

Of course your select worked pretty well ;-)
Only the time it took.... I tested it on a table of 4 million records.
It took way too long.
I thought there was maybe a way (or trick) for a fast count (and select
for that matter).
And yes Nigel, I need the count. Need it for a result set like Google.
Always wondered how they do it. But that's probably way of topic here. I
suspect they set up their tables and indices totally different then I, a
mere mortal, am doing.

Dennis


Alexandre Benson Smith wrote:

> 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
>
>
> *Yahoo! Groups Sponsor*
> ADVERTISEMENT
> click here
> <http://us.ard.yahoo.com/SIG=129tr6gap/M=298184.6018725.7038619.3001176/D=groups/S=1705115386:HM/EXP=1109722440/A=2593423/R=0/SIG=11el9gslf/*http://www.netflix.com/Default?mqso=60190075>
>
>
>
> ------------------------------------------------------------------------
> *Yahoo! Groups Links*
>
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> * To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
> <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/>.
>
>