CODEWITHSUNDEEP
phpmysqlsql
Olaf Erlandsen
Olaf Erlandsen

Reputation: 6036

MySQL - sort results by total time

i have three tables( runners, stages and time)

Runners table:

+--+----+
|id|name|
+--+----+
|1 |Karl|
+--+----+
|2 |Lou |
+--+----+

Stage Table:

+--+-----+-----+---+
|id|name |order|end|
+--+-----+-----+---+
|1 |start|  1  | 0 |
+--+-----+-----+---+
|2 |bike |  2  | 0 |
+--+-----+-----+---+
|3 |run  |  3  | 0 |
+--+-----+-----+---+
|4 |end  |  4  | 1 |
+--+-----+-----+---+

Runners data(time) Table:

+------+-----+-----+
|runner|stage|time |
+------+-----+-----+
|   1  |  1  |10:00|
+------+-----+-----+
|   1  |  2  |10:30|
+------+-----+-----+
|   1  |  3  |11:00|
+------+-----+-----+
|   2  |  1  |10:00|
+------+-----+-----+
|   2  |  2  |10:43|
+------+-----+-----+
|   2  |  3  |11:56|
+------+-----+-----+
|   1  |  4  |12:14|
+------+-----+-----+
|   2  |  4  |12:42|
+------+-----+-----+

Well ... then what I want now is to get the results as follows( order by total time ):

+------+-----+-----+-----+-----+----------+
|runner|start|bike |run  | end |   Total  |
+------+-----+-----+-----+-----+----------+
| Karl |10:00|10:30|11:00|12:14| 01:44:00 |   <--- FIRST( one hour)
+------+-----+-----+-----+-----+----------+
| Lou  |10:30|10:30|11:56|12:42| 02:12:00 |   <--- SECONDS( two hours )
+------+-----+-----+-----+-----+----------+

Have any idea how I can accomplish this? Greetings!

Upvotes: 1

Views: 132

Answers (4)

Jeremy Cook
Jeremy Cook

Reputation: 22103

You would probably need to do a lot of inner joining, subquerying, and comparing this time vs. that time if you want to go with that schema, and it really won't be pretty. Alternatively if your stages are fixed you could simplify to one table with each column as a stage. If the number and names of stages need to vary (for whatever reason) then I'd suggest storing a start time and end time in your runners date/time table.

If your stages are fixed then getting the result you are looking for straight out of the database will be easy. If the stages can vary (depending on your site users configuring stages for example) then you'll want to cross-tab your data in PHP or look at this SO question if you insist on doing it in the database (which I'd discourage).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270663

This requires a join and then conditional aggregation. The final column uses timediff() to subtract the two times:

select r.name,
       max(case when rt.stage = 1 then rt.time end) as start,
       max(case when rt.stage = 2 then rt.time end) as walk,
       max(case when rt.stage = 3 then rt.time end) as bike,
       max(case when rt.stage = 4 then rt.time end) as end,
       timediff(max(case when rt.stage = 4 then rt.time end),
                max(case when rt.stage = 1 then rt.time end)
               ) as TotalTime
from RunnersTime rt join
     Runners r
     on rt.runner = r.id 
group by r.id
order by TotalTime;

Note that the column names are fixed, so the stages table is not used. Making them dynamic would make the query much more complicated.

Upvotes: 1

rdans
rdans

Reputation: 2157

The query would look something like this but the method for calculating the total depends on the data type of the time.

select runners.name as runner, starttime.time as start, biketime.time as bike, runtime.time as run, endtime.time as end, endtime.time - starttime.time as Total

from runners

inner join time as starttime on runners.id = starttime.runner
inner join stages as startstages on starttime.stage = startstages.id and startstages.name = 'start'

inner join time as biketime on runners.id = biketime.runner
inner join stages as bikestages on biketime.stage = bikestages.id and bikestages.name = 'bike'

inner join time as runtime on runners.id = runtime.runner
inner join stages as runstages on runtime.stage = runstages.id and runstages.name = 'run'

inner join time as endtime on runners.id = endtime.runner
inner join stages as endstages on endtime.stage = endstages.id and endstages.name = 'end'

order by endtime.time - starttime.time

Upvotes: 1

nbar
nbar

Reputation: 6158

the following should work (times are in seconds, not in HH:MM:SS)

select r.name, rd_start.time as start, rd_bike.time as bike, rd_run.time as run, rd_end.time as end,  from runner as r, rd_start.time+rd_bike.time+rd_run.time+rd_end.time as total
inner join runnerdata as rd_start on r.id=rd_start.runner and rd_start.stage=1
inner join runnerdata as rd_bike on r.id=rd_bike.runner and rd_start.stage=2
inner join runnerdata as rd_run on r.id=rd_run.runner and rd_start.stage=3
inner join runnerdata as rd_end on r.id=rd_end.runner and rd_start.stage=4
order by (rd_start.time+rd_bike.time+rd_run.time+rd_end.time)

(If you post the create tables or even better use this tool: http://sqlfiddle.com/ it would make it easier for us to test our statements)

Upvotes: 1

Related Questions