Sorry, copy and paste error, the last line of the select wasn't copied,
so here's the full SQL:
SELECT a.Race, a.PosiForm, a.Selection;
FROM c_Race a;
INNER JOIN ;
(SELECT Race, MIN( Posiform) Posiform ;
FROM c_Race ;
GROUP BY Race;
WHERE NOT ISNULL(Posiform)) b;
ON a.Race = b.Race AND a.Posiform = b.posiform;
Frank.
Frank Cazabon
On 06/03/2013 02:48 PM, Frank Cazabon wrote:
How's this:
CREATE CURSOR c_Race;
(Race C(40),;
Selection c(20),;
Posiform n(4,2) null)
INSERT INTO c_Race VALUES ("2013-03-06 1400 Ling 1m4f Claim Stks",
"Stand Guard", 0.34)
INSERT INTO c_Race VALUES ("2013-03-06 1400 Ling 1m4f Claim Stks",
"Gower Rules", 0.54)
INSERT INTO c_Race VALUES ("2013-03-06 1400 Ling 1m4f Claim Stks",
"Right Stuff", 0.59)
INSERT INTO c_Race VALUES ("2013-03-06 1400 Ling 1m4f Claim Stks",
"Bashama", 0.68)
INSERT INTO c_Race VALUES ("2013-03-06 1410 Font 2m6f Nov Hrd",
"Heronry", 0.26)
INSERT INTO c_Race VALUES ("2013-03-06 1410 Font 2m6f Nov Hrd", "Boy
Of Boru", 0.60)
INSERT INTO c_Race VALUES ("2013-03-06 1410 Font 2m6f Nov Hrd",
"Cinematique", 0.96)
INSERT INTO c_Race VALUES ("2013-03-06 1410 Font 2m6f Nov Hrd", "Enter
Paradise", 0.38)
INSERT INTO c_Race VALUES ("2013-03-06 1410 Font 2m6f Nov Hrd", "Folly
Farm", 0.76)
INSERT INTO c_Race VALUES ("2013-03-06 1410 Font 2m6f Nov Hrd", "Lower
Hope Dandy", 0.31)
INSERT INTO c_Race VALUES ("2013-03-06 1410 Font 2m6f Nov Hrd", "Noble
Friend", 0.33)
INSERT INTO c_Race VALUES ("2013-03-06 1410 Font 2m6f Nov Hrd", "The
Royal Brompton", 0.83)
INSERT INTO c_Race VALUES ("2013-03-06 1410 Font 2m6f Nov Hrd", "Well
Sprung", 0.64)
INSERT INTO c_Race VALUES ("2013-03-06 1420 Catt 2m Sell Hrd",
"Tayarat", 0.67)
INSERT INTO c_Race VALUES ("2013-03-06 1420 Catt 2m Sell Hrd", "Billy
Thiss", 0.81)
INSERT INTO c_Race VALUES ("2013-03-06 1420 Catt 2m Sell Hrd",
"Gumnd", 0.73)
INSERT INTO c_Race VALUES ("2013-03-06 1420 Catt 2m Sell Hrd", "King
Fingal", 0.39)
INSERT INTO c_Race VALUES ("2013-03-06 1420 Catt 2m Sell Hrd",
"Mezarat", 0.49)
INSERT INTO c_Race VALUES ("2013-03-06 1420 Catt 2m Sell Hrd", "No
Quarter", 0.48)
INSERT INTO c_Race VALUES ("2013-03-06 1420 Catt 2m Sell Hrd", "Rare
Coincidence", 0.76)
INSERT INTO c_Race VALUES ("2013-03-06 1420 Catt 2m Sell Hrd", "Inis
Beag", null)
INSERT INTO c_Race VALUES ("2013-03-06 1420 Catt 2m Sell Hrd",
"Giveitago", 0.71)
INSERT INTO c_Race VALUES ("2013-03-06 1420 Catt 2m Sell Hrd",
"Landown Littlerock", 0.60)
INSERT INTO c_Race VALUES ("2013-03-06 1420 Catt 2m Sell Hrd", "Star
Kingdom", 0.64)
INSERT INTO c_Race VALUES ("2013-03-06 1420 Catt 2m Sell Hrd", "Rano
Pano", 0.42)
SELECT a.Race, a.PosiForm, a.Selection;
FROM c_Race a;
INNER JOIN ;
(SELECT Race, MIN( Posiform) Posiform ;
FROM c_Race ;
GROUP BY Race;
WHERE NOT ISNULL(Posiform)) b;
Frank.
Frank Cazabon
On 06/03/2013 02:02 PM, Garry Bettle wrote:
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/[email protected]
** 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.
