Thanks Helen,

As for using SELECT FIRST.... I'm assuming this won't buy me much as it
will still need to perform the complete query and then grab the first in
the result set.

Am I wrong in thinking that this isn't much better than...

rs = statement.executeQuery( "SELECT COUNT(*) INTO count FROM Foo WHERE
Foo.bar = 'bar';" );
rs.next();
if ( rs.getInt( 1 ) != 0 )
one_or_more_exist;

I can't use a Stored Procedure because the query will be dynamically
built by my object-model.

As for the sub-query, I'm not sure I understand the EXISTS keyword. I
didn't find much info on it in the Language Reference. But can the outer
select be a "fake" table, just to dummy a return from the subquery?

Am I going through too much effort just to avoid counting records? I
guess it just seems wastefull to perform a count of everything when all
I care is whether it exists or not (i.e. stop at the first and don't
count anymore after that).

R.



-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Monday, June 09, 2003 5:19 PM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] SELECT first


At 04:48 PM 9/06/2003 -0700, you wrote:

>What is the fastest way to find the first matching record for a select
>query without having to count all of them. I just want to know if even

a

>single match exists. My understanding is that COUNT has to continue
>through the entire table.

Assuming you are using Firebird ---

If you want to fetch the data of the first matching record, you'll need
to
include an ORDER BY clause to determine what "first" means.

SELECT FIRST 1 <column-list>
FROM <table>
ORDER BY <ordering-column>

If you only want to test whether any matching row exists in a different
table, then use a subquery with the WHERE EXISTS( ) predicate:

SELECT <column-list>
FROM <this-table>
WHERE EXISTS (
SELECT 1 FROM <that-table>
WHERE <search-criteria-for-that-table>)

In a stored procedure or trigger, you can also test using
IF (EXISTS (SELECT... ))

heLen



Yahoo! Groups Sponsor



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.