Re: More from select distinct than I can probably expect but here goes:

Tue, 10 Dec 2002 14:10:50 -0800

Brian, 
 
Try:
SELECT ridernum, fname, lname, MAX(ridemiles) FROM rides GROUP BY ridernum, fname, lname 
 
Randy Peterson
 
PS.  It looks like RiderNum always has the same FName and LName.  That presents some options if you use DISTINCT (RiderNum) and a lookup for (FName & LName).
 
You might also try:  SELECT DISTINCT ( (CTXT(ridernum)) & fname & lname) , MAX(ridemiles) FROM rider GROUP BY ridernum, fname, lname
 
----- Original Message -----
Sent: Tuesday, December 10, 2002 12:22 PM
Subject: RE: More from select distinct than I can probably expect but here goes:

I am scratching my head on this Select Distinct problem.
More from select distinct than I can probably expect but here goes:

Date looks like: (partial data, thousands of rows) (TABLE NAME :RIDES)

RiderNum        Fname   Lname       RideMiles              RideDate     GroupNumber
------------    -----------     --------------------    --------------  ----------------        
          1     Brian   Mowers          30.             10/21/2002      8
          1     Brian   Mowers          32.             11/24/2002      8
          2     Cathy   Mowers          30.             10/21/2002      8
          2     Cathy   Mowers          32.             11/24/2002      8
          8     Gunthe  Heurl           30.             10/21/2002      8
          8     Gunthe  Heurl           32.             11/24/2002      8
          9     Barb    Underwood       30.             10/21/2002      8
          9     Barb    Underwood       32.             11/24/2002      8
         22     Robert  Redford         30.             10/21/2002      8
         22     Robert  Redford         32.             11/24/2002      8
         41     Roger   Faber           30.             10/21/2002      8
         41     Roger   Faber           32.             11/24/2002      8
         48     Terrie  Faber           30.             10/21/2002        8
         48     Terrie  Faber           32.             11/24/2002        8

Want distinct Fname,Lname From Rides Where GroupNumber=8
 However, I want to include the RideMiles as part of this select.
If I put the RideMiles into the select (below) it breaks the discinctness that I am looking for. Is this possible?

This is what I want:
RiderNum        Fname   Lname           RideMiles       
--------------- ----------- ------------        --------------  
          1     Brian   Mowers          32.             
          2     Cathy   Mowers          32.             
          8     Gunthe  Heurl           32.             
          9     Barb    Underwood       32.             
         22     Robert  Redford         32.             
         41     Roger   Faber           32.             
         48     Terrie  Faber           32.             
Any insight appreciated.
Thanks,
Brian Mowers



Reply via email to