Fine-tuning Ranking for a Game System

Asked

Viewed 75 times

0

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

A while ago I posted a question almost like this but and they helped me a lot (I really appreciate it) but my need changed and now I have another table and my consultation has changed a lot, I asked a teacher for help and we came to this: 

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 am free MYSQL with Workbench and the error is this:

ERROR CODE: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 score was entered (ja BD the commands to automatically save date and time in the Insert and update points) the most recent score being given as the highest.

Once again I ask for help.

mysql query select

  • Only with this it is difficult to understand what you want. Show how your tables are and explain better what you want. id_jogador=7 is the basis for all calculations? Will the value be passed or will it always be 7? Taking advantage, make a [tour] and see better how the site works.

  • Hello Luis. If you still need help, please edit your question by adding more details. Hug!

1 answer

1

The error already says it all. You need to set 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;

Source

Browser other questions tagged mysql query select

You are not signed in. Login or sign up in order to post.