I've build a small database for my swimmingclub. It consists of 4
tables:
participants (e.g. participant 1, 2, 3 etc.)
competitions (e.g. swimming contest 1, 2, 3 etc.)
programs (e.g. 100m breaststroke, 200m butterfly)
times (participant 1 - swimming contest 1 - 100m breaststroke - 1.23,2
participant 2 - swimming contest 1 - 50m butterfly 0.30,3 )
The times table has one-many relations with the participants,
competitions, and programs table. (1 swimmer can do many programs, a
program can be swum by many many participants)
-> I a want to make a query in which the fastest time is selected for
every program (e.g. the club records).
* When I use the min-function in the times column, i get the fastest
time regardless of the program (useless)
* I can use the min-function in the times column, with the selection
criterium equal to one of the programs (50m breast, 100m butterfly).
But then I have to repeat this query for every program. Can this be
simplified?
* When I sort the times by program, and then use the minum on the
times column, i get the following error: You're trying to run a query,
in which the expression programname is not part of a statistical
function.
* I could sort by program, then by time, and take the top-values. But
if i use the top-values function I get only the fastest time.
-> Is there a function, which take the minimum given some criterium
(e.g for every program that exists)?
* When I make a cross-query, I can put the programs as a row, and some
artificial function which selects all records as a column (or vice
versa), and using the min-function for the (time)values, i get the
fastest time for every program, but not the name (participant) who set
that time.
* I can make a cross-query with the participants as a row, and the
programs as a column, and the min function over the time values to get
the personal records.
tables:
participants (e.g. participant 1, 2, 3 etc.)
competitions (e.g. swimming contest 1, 2, 3 etc.)
programs (e.g. 100m breaststroke, 200m butterfly)
times (participant 1 - swimming contest 1 - 100m breaststroke - 1.23,2
participant 2 - swimming contest 1 - 50m butterfly 0.30,3 )
The times table has one-many relations with the participants,
competitions, and programs table. (1 swimmer can do many programs, a
program can be swum by many many participants)
-> I a want to make a query in which the fastest time is selected for
every program (e.g. the club records).
* When I use the min-function in the times column, i get the fastest
time regardless of the program (useless)
* I can use the min-function in the times column, with the selection
criterium equal to one of the programs (50m breast, 100m butterfly).
But then I have to repeat this query for every program. Can this be
simplified?
* When I sort the times by program, and then use the minum on the
times column, i get the following error: You're trying to run a query,
in which the expression programname is not part of a statistical
function.
* I could sort by program, then by time, and take the top-values. But
if i use the top-values function I get only the fastest time.
-> Is there a function, which take the minimum given some criterium
(e.g for every program that exists)?
* When I make a cross-query, I can put the programs as a row, and some
artificial function which selects all records as a column (or vice
versa), and using the min-function for the (time)values, i get the
fastest time for every program, but not the name (participant) who set
that time.
* I can make a cross-query with the participants as a row, and the
programs as a column, and the min function over the time values to get
the personal records.
Comment