MySQL: Group PlayerUID, Show total kills and latest coordinates of alive character -

MySQL: Group PlayerUID, Show total kills and latest coordinates of alive character -

i've been wrestling while , haven't found solution.

i have mysql database table contains game character data, next columns:

characterid: 1 time player dies, gets new character, adds new entry. playeruid: unique each player. need grouping that. killsz: shows how many kill each character has had. worldspace: position of character. alive: character live or dead?

so need sql query returns total kills player , coordinates of current character. how this?

this think should be:

select characterid, playeruid, sum(killsz), worldspace, live character_data worldspace in (select worldspace character_data live = 1) grouping playeruid order sum(killsz) desc;

output: (11 top rows)

"characterid" "playeruid" "sum(killsz)" "worldspace" "alive" "924" "76561198042033373" "88" "[128,[8438.99,3093.65,7.408]]" "1" "509" "76561198117519944" "78" "[234,[4284.56,6222.69,-4.425e-04]]" "1" "1360" "76561198046918433" "63" "[194,[8735.35,5139.39,16.179]]" "1" "1457" "76561197988106961" "46" "[146,[8740.07,5089.61,0.278]]" "1" "606" "76561198050960639" "40" "[317,[4523.15,1815.6,0.889]]" "1" "1413" "76561198053078285" "37" "[303,[5908.08,2024.8,0.002]]" "1" "597" "76561198118406618" "36" "[338,[4433.23,1619.99,0.001]]" "1" "99" "76561197988519309" "32" "[]" "0" "1384" "76561198088203111" "31" "[205,[2334.34,8904.73,0.001]]" "1" "375" "76561197965753706" "30" "[307,[3962.21,7522.75,7.299]]" "1" "1364" "76561198013225036" "28" "[195,[8452.11,3093.67,0.198]]" "1"

and here query without:

select characterid, playeruid, sum(killsz), worldspace, live character_data grouping playeruid order sum(killsz) desc;

which outputs: (11 top rows)

"characterid" "playeruid" "sum(killsz)" "worldspace" "alive" "14" "76561198034271930" "982" "[96,[6462.44,3728.73,0]]" "0" "3" "76561198053078285" "754" "[106,[6024.94,1215.49,0]]" "0" "12" "76561197988519309" "427" "[147,[2387.11,4747.59,0.002]]" "0" "48" "76561198022644097" "383" "[11,[7879.52,1695.43,3.368]]" "0" "636" "76561198055683105" "344" "[184,[5063.51,3356.11,0.001]]" "0" "635" "76561198084556053" "312" "[311,[8730.96,5077.45,0.001]]" "0" "855" "76561198096335334" "262" "[191,[7116.15,2484.91,0]]" "0" "560" "76561198082709294" "247" "[234,[1967.64,5471.63,0.002]]" "0" "8" "76561198154755873" "217" "[99,[5438.96,3317.55,0.002]]" "0" "96" "76561197961156126" "206" "[117,[2597.8,3383.29,0.876]]" "0" "806" "76561198007739284" "180" "[312,[6502.08,3744.86,0.003]]" "0"

you can see total kills different.

when group by result set, standard sql requires using aggregate functions of columns not in group by clause. because there multiple rows corresponding each group, different (or potentially different) values in non-grouping columns. of these values query refer to? other dbms not process ambiguous query 1 in question, mysql allows this under assumption ever when of possible values in non-grouping columns constant within group.

it sounds want total kills of characters associated player, easy summing killsz column on each group:

select playeruid, sum(killsz) totalkills character_data grouping playeruid;

to add together coordinates of currently-alive character, utilize case look nullify coordinates in each row corresponding dead character, , utilize aggregate function max take single value remains:

select playeruid, sum(killsz) total_kills, max(case when live = 1 worldspace end) current_coordinates character_data grouping playeruid;

this simple query makes 1 pass on info , satisfied appropriate covering index (if necessary performance). in comparison, solution suggests outer joining table complex satisfied smaller indexes, if that's important. 1 of import difference between 2 solutions mine show players have dead characters, while other written not.

this relies on of import assumption 1 row in grouping have alive = 1 @ given time. might want consider enforcing constraint within database. long player can have @ 1 character live in database, you'll same result either max or min , can utilize either one. if coordinates value numeric, utilize sum, though think bit misleading (and wouldn't work if coordinates stored char type).

in mysql can replace simple case look if(alive = 1, worldspace, null) since less portable , not less verbose, recommend sticking case part of sql standard.

mysql