Fine tuning in Ranking for a game system [closed]

0

I have a very crazy problem that has already burned my logic ability.

Some time ago I asked a question almost the same as this one but they helped me a lot (thank you very much) but my need has changed and now I have another table and my query has changed a lot, I asked a teacher for help and we came to this: / p>

select * from (
(SELECT distinct nome_jogador,pontos,datahora, 1 as ordem
from jogador j inner join pontuacao_fx p 
on j.id_jogador = p.id_jogador  WHERE pontos > (SELECT pontos FROM pontuacao_fx WHERE id_jogador=7 )
ORDER BY datahora desc, pontos desc)

union

(select distinct nome_jogador, pontos,datahora, 2 as ordem
from jogador j inner join pontuacao_fx p 
on j.id_jogador = p.id_jogador where pontos = (SELECT pontos FROM pontuacao_fx WHERE id_jogador=7 )
)

union

(SELECT distinct nome_jogador,pontos,datahora, 3 as ordem
from jogador j inner join pontuacao_fx p 
on j.id_jogador = p.id_jogador WHERE pontos < (SELECT pontos FROM pontuacao_fx WHERE id_jogador=7 )
ORDER BY datahora desc, pontos desc);

) order by ordem asc,datahora desc, pontos desc

I'm free MYSQL with workbench and the error is this:

  

CODE ERROR: 1248: EVERY DERIVED TABLE MUST HAVE ITS OWN ALIAS.

I have to do this ranking now with the rating by score and if there is a tie, by date and time the punctuation was inserted (already done in BD the commands to save date and time automatically in insert and update of points ) with the most recent score being given as highest.

Once again I ask for help.

    
asked by anonymous 23.02.2016 / 17:41

1 answer

1

The error says it all. You need to define an alias for your derived tables. Look at the example below:

SELECT SUM('quant'), MONTH('date') AS month, 'id' 
from (
  ... inner select 
) 
group by id, month;

The correct would be:

SELECT SUM('quant'), MONTH('date') AS month, 'id' 
from (
  ... inner select 
) as t -- this is the change
group by id, month;

Font

    
23.02.2016 / 18:35