Ah - Garry - I see U R working in Horse Racing data. I actually used to work for the Form, and previous to that the Racing Times - and even Figs Form!
I just tried your stuff below - Created a Cursor based upon your Data - and I tried your Query - then, I simply added the Selection field - and it seemed to return the correct results! SELECT Race, Selection, MIN(Posiform) FROM Table GROUP BY 1 WHERE Posi > 0 -K- -----Original Message----- From: ProfoxTech [mailto:[email protected]] On Behalf Of Garry Bettle Sent: Wednesday, March 06, 2013 1:03 PM To: [email protected] Subject: Select Min & Return more than the Group By Howdy all, Hope this message finds everyone well. I have the following table: Race Selection Posiform 2013-03-06 1400 Ling 1m4f Claim Stks Stand Guard 0.34 2013-03-06 1400 Ling 1m4f Claim Stks Gower Rules 0.54 2013-03-06 1400 Ling 1m4f Claim Stks Right Stuff 0.59 2013-03-06 1400 Ling 1m4f Claim Stks Bashama 0.68 2013-03-06 1410 Font 2m6f Nov Hrd Heronry 0.26 2013-03-06 1410 Font 2m6f Nov Hrd Boy Of Boru 0.60 2013-03-06 1410 Font 2m6f Nov Hrd Cinematique 0.96 2013-03-06 1410 Font 2m6f Nov Hrd Enter Paradise 0.38 2013-03-06 1410 Font 2m6f Nov Hrd Folly Farm 0.76 2013-03-06 1410 Font 2m6f Nov Hrd Lower Hope Dandy 0.31 2013-03-06 1410 Font 2m6f Nov Hrd Noble Friend 0.33 2013-03-06 1410 Font 2m6f Nov Hrd The Royal Brompton 0.83 2013-03-06 1410 Font 2m6f Nov Hrd Well Sprung 0.64 2013-03-06 1420 Catt 2m Sell Hrd Tayarat 0.67 2013-03-06 1420 Catt 2m Sell Hrd Billy Thiss 0.81 2013-03-06 1420 Catt 2m Sell Hrd Gumnd 0.73 2013-03-06 1420 Catt 2m Sell Hrd King Fingal 0.39 2013-03-06 1420 Catt 2m Sell Hrd Mezarat 0.49 2013-03-06 1420 Catt 2m Sell Hrd No Quarter 0.48 2013-03-06 1420 Catt 2m Sell Hrd Rare Coincidence 0.76 2013-03-06 1420 Catt 2m Sell Hrd Inis Beag 2013-03-06 1420 Catt 2m Sell Hrd Giveitago 0.71 2013-03-06 1420 Catt 2m Sell Hrd Landown Littlerock 0.60 2013-03-06 1420 Catt 2m Sell Hrd Star Kingdom 0.64 2013-03-06 1420 Catt 2m Sell Hrd Rano Pano 0.42 ... I'd like the Min ( PosiForm) for each Race group. That's no problem: SELECT Race, MIN( Posiform) FROM table GROUP BY 1 WHERE Posiform > 0 Which returns: Race Min_posiform 2013-03-06 1400 Ling 1m4f Claim Stks 0.34 2013-03-06 1410 Font 2m6f Nov Hrd 0.26 2013-03-06 1420 Catt 2m Sell Hrd 0.39 But, what I'd really like is to also have the Selection column returned. Like this: Race Selection Min_posiform 2013-03-06 1400 Ling 1m4f Claim Stks Stand Guard 0.34 2013-03-06 1410 Font 2m6f Nov Hrd Heronry 0.26 2013-03-06 1420 Catt 2m Sell Hrd King Fingal 0.39 I can do it okay in a prg*. Is it possible in a SQL Select? Perhaps a join of some sort, or maybe a SubQuery ... ? Many thanks in advance. Cheers, Garry * Prg: CLOSE ALL USE table IN 0 ALIAS Sl CREATE CURSOR curTemp ( Race c( 50), Selection c( 30), PosiForm n( 7, 2)) INDEX on Race TAG Race SELECT Sl DO WHILE !EOF( [Sl]) lcRace = Sl.SubDir lnLowestPForm = IIF( Sl.Posiform > 0, Sl.Posiform, 999) lcSelection = Sl.Selection DO WHILE !EOF( [SL]) AND lcRace = Sl.SubDir IF Sl.Posiform > 0 AND Sl.Posiform < lnLowestPForm lnLowestPForm = Sl.Posiform lcSelection = Sl.Selection ENDIF SKIP ENDDO IF lnLowestPForm < 999 INSERT INTO curTemp ( Race, Selection, PosiForm) VALUES ( lcRace, lcSelection, lnLowestPForm) ELSE INSERT INTO curTemp ( Race, Selection) VALUES ( lcRace, [*** No Selection ***], lnLowestPForm) ENDIF ENDDO SELECT curTemp BROWSE --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/8A2EFD9E9B6044C6BB373D74AC14DD5C@Programming2 ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
