MYSQL Diff From MAX -

-

i'm trying query motogp result here's sqlfiddle

i want add 2 more columns

  1. pos (1, 2, 3, 4, ...)
  2. gap (rider laptime - 1st pos laptime)

desired result:

  • 1 | marquez | 2799.627 | 2799.627
  • 2 | rossi |2803.143 | 3.516

enter image description here

my current query:

select `rider`, sum(laptime), count(`lapno`), max(`topspeed`) ts     (select      `lapno`,`rider`, (t1+t2+t3+t4) laptime , `topspeed`      `a_lap_time`  t_laptime)  group a.rider order count(`lapno`) desc, sum(laptime) asc

please advise, thank you

this result

|           rider |  laptime |    gap | lapno |    ts | |-----------------|----------|--------|-------|-------| |    marc marquez | 2799.627 |      0 |    30 | 329.3 | | valentino rossi | 2803.143 |  3.516 |    30 | 319.7 |

produced by:

select     `rider`, laptime, laptime - minlaptime gap, lapno, ts (       select            `rider`, laptime, lapno, ts, @lap := if(@lap = 0.0, laptime, @lap) minlaptime       (             select `rider`, sum(laptime) laptime , count(`lapno`) lapno                  , max(`topspeed`) ts                             (select                  `lapno`,`rider`, (t1+t2+t3+t4) laptime , `topspeed`                 `a_lap_time`  t_laptime                  )              group a.rider             ) b       cross join (select @lap :=0) var       order `lapno` desc, laptime asc       ) c

a variable: @lap "attached" each row using cross join, using order minimum overall laptime pass value @lap. once done have 2799.627 value on each row , can calculate gap.

see sqlfiddle: http://sqlfiddle.com/#!9/bd71f/6

for pos well:

select     `rider`, laptime, laptime - minlaptime gap, lapno, ts, pos (       select              `rider`, laptime, lapno, ts            , @lap := if(@lap = 0.0, laptime, @lap) minlaptime            , @pos := @pos + 1 pos       (             select `rider`, sum(laptime) laptime , count(`lapno`) lapno                  , max(`topspeed`) ts                             (select                  `lapno`,`rider`, (t1+t2+t3+t4) laptime , `topspeed`                 `a_lap_time`  t_laptime                  )              group a.rider             ) b       cross join (select @lap :=0, @pos := 0) var       order `lapno` desc, laptime asc       ) c

result:

|           rider |  laptime |   gap | lapno |    ts | pos | |-----------------|----------|-------|-------|-------|-----| |    marc marquez | 2799.627 |     0 |    30 | 329.3 |   1 | | valentino rossi | 2803.143 | 3.516 |    30 | 319.7 |   2 |

Comments

Post a Comment

Popular posts from this blog

How to show in django cms breadcrumbs full path? -

-
need advice - how show in breadcrumbs whole path article or page? i've got code: {% ance in ancestors %} <li> {% if not forloop.last %} <a href="{{ ance.get_absolute_url }}">{{ ance.get_menu_title }}</a> {% else %} <span class="active">{{ ance.get_menu_title }}</span> {% endif %} </li> {% endfor %} but shows "home" , current page. how modify show whole path in breadcrumbs? i've found in file menu_tags.py code calls ancestors. @ least there class showbreadcrumbs: class showbreadcrumb(inclusiontag): """ shows breadcrumb node has same url current request - start level: after level should breadcrumb start? 0=home - template: template used render breadcrumb """ name = 'show_breadcrumb' template = 'menu/dummy.html' options = options( argument('start_level', default=0, require...
Read more

php - Invalid Cofiguration - yii\base\InvalidConfigException - Yii2 -

-
Image
i've installed yii2-user module/dektrium yii2-app-basic application command composer require "dektrium/yii2-user:0.9.*@dev" config/console.php return [ . . 'modules' => [ 'gii' => 'yii\gii\module', 'user' => [ 'class' => 'dektrium\user\module', ], ], . . ] config/web.php 'components' => [ . . /* 'user' => [ 'identityclass' => 'app\models\user', 'enableautologin' => true, ], */ 'modules' => [ 'user' => [ 'class' => 'dektrium\user\module', ], ], . . ] after that, run command $ php yii migrate/up --migrationpath=@vendor/dektrium/yii2-user/migrations updating database schema . but, when run http://localhost/mylawsuit/yii/web/index.php?r=u...
Read more

ruby on rails - npm error: tunneling socket could not be established, cause=connect ETIMEDOUT -

-
i have app uses rails framework , implements angularjs part of front end. i have pushed heroku , have heroku toolbelt installed, when try migrate db using "heroku run rake db:migrate" receive following error(s): installing core plugins heroku-cli-addons, heroku-apps, heroku-fork, heroku-git, heroku-local, heroku-run, heroku-status... error installing package. try running again gode_debug=info see more output. ! `run` not heroku command. ! perhaps meant `-h`, `2fa`, `auth`, `join`, `open`, `orgs`, `pg`, `ps` or `rake`. ! see `heroku help` list of available commands. i run command "gode_debug=info heroku run rake db:migrate" , receive error: npm err! darwin 14.5.0 npm err! argv "/users/christopher_pelnar/.heroku/node-v4.2.1-darwin-x64/bin/node" "/users/christopher_pelnar/.heroku/node-v4.2.1-darwin-x64/lib/node_modules/npm/cli.js" "install" "heroku-cli-addons" "heroku-apps" "heroku-fork...
Read more