Hi Harry!

In general, I don't think doing a SELECT COUNT... on a SELECT FIRST
makes much sense. In your case, it is possible to get the correct
result, but in general getting trouble with a SELECT COUNT indicates
that you come from a desktop database background and use SELECT COUNT
where it is not needed (client/server database programmers tend to
just count the records as we fetch them). Here's the query that will
fix your problem the way you seem to want to do things:

SELECT T.IndexKey, T.AccountNo FROM TARIFF T
WHERE T.IndexKey >= '020110 '
AND NOT EXISTS(SELECT * FROM TARIFF T1
WHERE T1.IndexKey >= '020110 '
AND (T1.IndexKey < T.IndexKey
OR (T1.IndexKey = T.IndexKey
AND T1.AccountNo < T.AccountNo))
AND EXISTS(SELECT * FROM TARIFF T2
WHERE T2.IndexKey >= '020110 '
AND (T2.IndexKey < T1.IndexKey
OR (T2.IndexKey = T1.IndexKey
AND T2.AccountNo < T1.AccountNo))
AND EXISTS(SELECT * FROM TARIFF T3
WHERE T3.IndexKey >= '020110 '
AND (T3.IndexKey < T2.IndexKey
OR (T3.IndexKey = T2.IndexKey
AND T3.AccountNo < T2.AccountNo))
AND EXISTS(SELECT * FROM TARIFF T4
WHERE T4.IndexKey >= '020110 '
AND (T4.IndexKey < T3.IndexKey
OR (T4.IndexKey = T3.IndexKey
AND T4.AccountNo < T3.AccountNo))
AND EXISTS(SELECT * FROM TARIFF T5
WHERE T5.IndexKey >= '020110 '
AND (T5.IndexKey < T4.IndexKey
OR (T5.IndexKey = T4.IndexKey
AND T5.AccountNo < T4.AccountNo)))))))

With this query, the record count should give you the result you want
if the combination IndexKey, AccountNo is unique. If IndexKey by
itself is unique, you may remove ten lines of the query whereas if you
are only showing us a simplified part of your query you may have to do
some more coding to make things work. The good news is that with good
indexes, [NOT] EXISTS is usually pretty quick (though I've never tried
nesting it five levels deep), but are you still certain that you want
to do a record count on the server?

HTH,
Set

--- In firebird-support@yahoogroups.com, "draconian2800" wrote:
> Hi All!!
>
> How can I get dbexpress in delphi to return only the selected amount
> of rows in the recordcount and not process the entire database and
> return the entire recordcount for that database?
>
> I use a simple TSQLQUERY component with :
>
> SELECT FIRST 5 IndexKey,AccountNo FROM TARIFF
> WHERE IndexKey >= '020110 '
> ORDER BY IndexKey;
>
> But with this I get a resultant recordcount of 8000 and not 5!! As a
> result our program is hellishly slow!!
>
> Please help!!
>
> Thanx!!
>
> Harry Roets!!