AnsweredAssumed Answered

Nested SQL Question

Question asked by cynthiablue on Jan 22, 2018
Latest reply on Jan 23, 2018 by okramis

Seeme there are a lot of Nested SQL questions... but I couldn't quite find the answer to what I'm looking for.

 

I need to get the Dog ID (__fkDog) of the run with the shortest runTime in my Run table (including a specified event and trial number in the Where clause).  The below gives me the dreaded ?.

What am I doing wrong that Filemaker is confused about? I'd appreciate any help.

 

ExecuteSQL ("

SELECT \"__fkDog\"

FROM Run

WHERE

   (SELECT min(runTime)

   FROM Run

   WHERE \"__fkEvent\" = CurrentEvent::__pkCurrentEvent

   AND TrialNumber = $tNum)

AND \"__fkEvent\" = CurrentEvent::__pkCurrentEvent

AND TrialNumber = $tNum

"; ""; ""

)

 

The following gives me a set of all dog IDs with their minimum run times... but I need just the one dog with the shortest run time.

 

ExecuteSQL ("

SELECT \"__fkDog\", min(runTime)

FROM Run

WHERE \"__fkEvent\" = ?

AND TrialNumber = ?

Group By \"__fkDog\"

"; ""; ""; CurrentEvent::__pkCurrentEvent; $tNum

)

 

 

Thanks!

Cyn

Outcomes