database - MySQL Order by before group by in view (No Sub-Querys) -

i realize question has been asked quite few times, haven't managed find working solution case.

essentially problem arises because mysql doesn't allow sub-querys in views. found few workarounds don't seem work.

in more detail...

my first table (competitions) stores users competitions:

id_tournament | id_competition | id_user | result -------------------------------------------------        1      |        1       |     1   |   10        1      |        1       |     2   |   30        1      |        2       |     1   |   20        1      |        2       |     3   |   50        1      |        3       |     2   |   90        1      |        3       |     3   |   100        1      |        3       |     4   |   85 

in example there 3 competitions:

(     user1 vs. user2,     user1 vs. user3,     user2 vs. user3 vs. user4 ) 

my problem need define view gives me winners in each competition.

expected result:

 id_tournament | id_competition | id_winner  ------------------------------------------         1      |        1       |     2            1      |        2       |     3           1      |        3       |     3    

this can solved query:

select      id_tournament,     id_competition,     id_user id_winner (      select * competitions order result desc  ) x group id_tournament, id_competition 

this query uses subquery (not allowed in views), first solution define 'helper view'as :

create view competitions_helper (     select * competitions order result desc );  create view competition_winners (     select          id_tournament,         id id_competition,         id_user winner     competitions_helper group id_tournament, id_competition ); 

however not seem give correct result. it's result be:

id_tournament | id_competition | id_winner ------------------------------------------        1      |        1       |     1           1      |        2       |     1        1      |        3       |     1 

what don't understand why works when use sub-querys , why gives different result exact same statement in view.

any appreciated, alot.

this due group by behaviour.

in case, server free choose value each group, unless same, values chosen indeterminate, not want.

i solve problem in way:

create view competitions_helper (     select      id_tournament,                 id_competition,                 max(result) winning_result            competitions     group    id_tournament,                 id_competition );  create view competition_winners (     select      c.id_tournament,                 c.id_competition,                 c.id_user            competitions c     inner join  competitions_helper ch             on  ch.id_tournament = c.id_tournament             , ch.id_competition = c.id_competition             , ch.winning_result = c.result );