Limit selection to first record

B

birdman

I have a query that may return several records, but I only want the First record of that query to be selected. How can I do that? I have tried using First but I keep getting an error that one of the other fields is not included. The SQL is below. Thanks for any help.

string varSQL="SELECT birdList.family, birdList.commonName, Photos.photoQuality, Photos.contributor, Identification.idCharacteristics FROM (birdList LEFT JOIN Photos ON birdList.commonName = Photos.commonName) INNER JOIN Identification ON birdList.commonName = Identification.commonName WHERE birdList.commonName=@param and Photos.permission=Yes ORDER BY birdList.family, birdList.commonName, Photos.photoQuality";
 
D

Douglas J. Steele

Try:

SELECT Top 1 birdList.family, birdList.commonName, Photos.photoQuality,
Photos.contributor, Identification.idCharacteristics FROM (birdList LEFT
JOIN Photos ON birdList.commonName = Photos.commonName) INNER JOIN
Identification ON birdList.commonName = Identification.commonName WHERE
birdList.commonName=@param and Photos.permission=Yes ORDER BY
birdList.family, birdList.commonName, Photos.photoQuality

Note that in case of a tie (i.e.: two or more records with the same value
for birdList.family, birdList.commonName and Photos.photoQuality), more than
one record will be returned.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


birdman said:
I have a query that may return several records, but I only want the First
Click to expand...
record of that query to be selected. How can I do that? I have tried using
First but I keep getting an error that one of the other fields is not
included. The SQL is below. Thanks for any help.
string varSQL="SELECT birdList.family, birdList.commonName,
Click to expand...
Photos.photoQuality, Photos.contributor, Identification.idCharacteristics
FROM (birdList LEFT JOIN Photos ON birdList.commonName = Photos.commonName)
INNER JOIN Identification ON birdList.commonName = Identification.commonName
WHERE birdList.commonName=@param and Photos.permission=Yes ORDER BY
birdList.family, birdList.commonName, Photos.photoQuality";
 
Top