RE: SQL - multiple rows for same customer, but only want earliest one

SELECT field1,field2,...
FROM file
WHERE criteria
ORDER BY transactiondate
FETCH FIRST ROW ONLY

That will return only the first row of your select. With the ORDER BY
clause, that row will be the one with the earliest transaction date.

Have fun!
Richard


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Dan
Sent: Thursday, January 11, 2007 2:47 PM
To: Midrange Systems Technical Discussion
Subject: SQL - multiple rows for same customer, but only want earliest one


I think I need an SQL boot camp.

I know this can be done, but the reference and/or programming guide need a
"If you want to do *this*, go here" type of chapter.

I have a select for which multiple rows can be returned for the same
customer number, but I only want the row that has the earliest transaction
date.  It appears that DISTINCT will not return an entire row, and neither
does MIN.  What am I missing?

TIA,
Dan
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.16.9/622 - Release Date: 1/10/2007
2:52 PM

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.16.9/622 - Release Date: 1/10/2007
2:52 PM