aggregate - MySQL - retrieving rows ordered by greatest value of a variable series of columns -

aggregate - MySQL - retrieving rows ordered by greatest value of a variable series of columns -

i having quite hard time particular mysql query. here construction of 3 tables need bring together , sort rows from:

users id username routes id val_1 val_2 val_3 val_4 scores id route_id user_id route_level

this query leaderboard feature, , needs homecoming list of user ids ordered best score. 1 user may have several scores, , need find best score.

the twist "route_level" part: if value of column illustration "2", need go corresponding route_id, , find greatest value between val_1 , val_2. must go no higher val_x, x beingness value of column scores.route_level.

also, val_x not int (the value "6a+", next bigger value beingness 6b, 6b+ etc.), , val_x not smaller val_x+1.

here far got, doesn't work (i results on place, not ordered @ all, @ to the lowest degree not in way can create sense of):

select u.*, r.val_1 v1, r.val_2 v2, r.val_3 v3, r.val_4 v4 users u inner bring together scores s on s.user_id = u.id inner bring together routes r on s.route_id = r.id grouping u.id order greatest(v1, v2, v3, v4) desc

does of have thought of how might take on in single mysql query?

thanks! :-)

edit: here sqlfiddle link

you need aggregate query right in order ordering right. seek this. handle aggregation user correctly, , pull maximum val_n score each user.

select u.id, u.username, max(r.val_1) v1, max(r.val_2) v2, max(r.val_3) v3, max(r.val_4) v4 users u inner bring together scores s on s.user_id = u.id inner bring together routes r on s.route_id = r.id grouping u.id ,u.username order greatest( max(r.val_1), max(r.val_2), max(r.val_3), max(r.val_4)) desc

edit missed bit route_level ... focused much on query in question, not text.

but it's not tremendously hard apply. needs applied row-by-row, @ detail level, before aggregation function max(). can done, believe, changing select part of query follows.

select u.id, u.username, max(greatest ( if(r.route_level>=1, r.val_1, null) if(r.route_level>=2, r.val_2, null) if(r.route_level>=3, r.val_3, null) if(r.route_level>=4, r.val_4, null) )) val users u inner bring together scores s on s.user_id = u.id inner bring together routes r on s.route_id = r.id grouping u.id ,u.username order max(greatest ( if(r.route_level>=1, r.val_1, null) if(r.route_level>=2, r.val_2, null) if(r.route_level>=3, r.val_3, null) if(r.route_level>=4, r.val_4, null) ))

i missed point val_n values beingness varchar() values rather integers. 1 harder. when utilize max() on varchar() values, employs collation. character collations know of declare value 6b+, example, larger 10b+, because 6 greater 1.

i sorry, don't know of elegant sql magic can create work better.

mysql aggregate