select first match

edojan

Registered User.
Local time
Today, 14:19
Joined
Dec 19, 2006
Messages
26
hi

how can i make query that selects the first match of a criteria, or multiple criteria. lets say list of members first record in CA only then first record in OR only .... basically select first record from list of criteria.
 
What do you mean by 'first'? If you mean any one match, then Select First will do it. However this will find the first record dependant on the order in the table. As tables are intrinsically unordered, this may not be the record you expect.
 
PHP:
As tables are intrinsically unordered . . .
is not true. Tables are ordered according to fields which are indexed. If no fields are indexed, records are in the order input.

SELECT FIRST merely gives the first record with the specified query and/or SQL, which can re-order the records in some other manner.
 
llkhoutx said:
PHP:
As tables are intrinsically unordered . . .
is not true. Tables are ordered according to fields which are indexed. If no fields are indexed, records are in the order input.

SELECT FIRST merely gives the first record with the specified query and/or SQL, which can re-order the records in some other manner.
Exactly, the order is imposed by the index, not the table. I think you're being a bit pedantic. My explanation was intended to explain to edojan that First (and Last) often do not return the expected record. In my experience, Max or Min used on a field which is capable of being ordered (even if it is not indexed) is a much more reliable approach.
 
With a query you can order the records, regardless of the indicies, and you'll know what to expect as the first record for specific criteria.
 
Yes, you can impose an order in a query, then First is predictable. But that's what I was saying earlier, if the table is unordered you don't know what you are going to get.

This thread started as some advice to a relative novice and you didn't explain that ordering the data was important. Max and Min work regardless of the order of the data.
 

Users who are viewing this thread

Back
Top Bottom