Geek Answers Handbook

It features a complex sql query

I have one more for you guys. I searched, but cannot understand the cause of this problem.

Basically, I want to make DISTINCT on user_id, which is a column that naturally joins and exists in the tables "user" and "leaderboard_entry".

This is my original query, which works great, but I would like to filter out duplicates and show only the first fastest user account. The inner query basically captures 100 of the last lines, and the outer query uses them in ascending leaderboard_entry_elapsed_time_ms. 

set @t1=0; select * from 
( 
   select @t1 := @t1+1 as leaderboard_entry_youngness_rank, 1-@t1/100 as 
     leaderboard_entry_youngness_based_on_expiry, 
   leaderboard_entry.*, 
   NOW()-leaderboard_entry_timestamp as leaderboard_entry_age_in_some_units , 
   TO_DAYS(NOW())-TO_DAYS(leaderboard_entry_timestamp) 
     as leaderboard_entry_age_in_days , leaderboard.leaderboard_quiz_mode , 
   leaderboard.leaderboard_load_key , 
   user.user_name 
   from leaderboard_entry 
   natural join
   leaderboard 
   natural join 
   user
   where 
   (leaderboard_load_key = 'es-en-animals-1' 
   or leaderboard_load_key = '-es-en-animals-1' ) 
   and leaderboard_quiz_mode = '0'
   order by leaderboard_entry_age_in_some_units asc , 
     leaderboard_entry_timestamp asc limit 0, 100 
) as outer_temp 
order by 
  leaderboard_entry_elapsed_time_ms asc , 
  leaderboard_entry_timestamp asc 
limit 0, 50

I tried the following, which removes "leaderboard_entry. *" And adds the DISTINCT keyword in the same way as explicitly indicating the columns you want: 

set @t1=0; select * from 
( 
   select @t1 := @t1+1 as leaderboard_entry_youngness_rank, 1-@t1/100 as 
     leaderboard_entry_youngness_based_on_expiry,

NOW()-leaderboard_entry_timestamp as leaderboard_entry_age_in_some_units , TO_DAYS(NOW())-TO_DAYS(leaderboard_entry_timestamp) as leaderboard_entry_age_in_days , leaderboard.leaderboard_quiz_mode , leaderboard.leaderboard_load_key , user.user_name

distinct leaderboard_entry.user_id, leaderboard_entry.leaderboard_entry_id, leaderboard_entry.leaderboard_id, leaderboard_entry.leaderboard_entry_timestamp, leaderboard_entry.leaderboard_entry_elapsed_time_ms,

from leaderboard_entry natural join leaderboard natural join user where (leaderboard_load_key = 'es-en-animals-1' or leaderboard_load_key = '-es-en-animals-1' ) and leaderboard_quiz_mode = '0' order by leaderboard_entry_age_in_some_units asc , leaderboard_entry_timestamp asc limit 0, 100 ) as outer_temp order by leaderboard_entry_elapsed_time_ms asc , leaderboard_entry_timestamp asc limit 0, 50

, ...:( 

#1064 - You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL 
server version for the right syntax to use near 
'distinct leaderboard_entry.user_id, leaderboard_entry.leaderboard_entry_id, ' 
at line 12

!

0
1

The Scrum Meister

0

More articles:


All Articles