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

Dan,

I thought you were only wanting a single row (earliest transaction date) for
a single customer. If you are wanting a result set containing multiple
customers, but only each customer's earliest transaction date, then one of
the other solutions mentioned is the way to go.

The full form of the FETCH clause is "FETCH FIRST nnn ROWS ONLY". That is
handy for selecting a subset of your complete result set. With ORDER BY xxx
and GROUP BY xxx/HAVING xxx, you can do all sorts of stuff. For example,
getting a list of the top 10 customers based on total number of transactions
is as easy as:

SELECT customer,count(*)
FROM transactionfile
GROUP BY customer
ORDER BY count(*) desc
FETCH FIRST 10 ROWS ONLY

Have fun!
Richard


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


Thanks for all of the replies!  I used a combination of Charles' and
Birgitta's solution.  (I needed to add Group By to Charles' sample.)

Richard, a different way to skin a cat, eh?  Only one row could ever be
returned, so not useful in my situation.  I'm curious though, under what
circumstances would FETCH FIRST ROW ONLY be "useful"?  Obviously, it works,
so somebody declared there would be a need, but I'm just trying to figure
out what that is.

- 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