SQL - Command for general classification

Asked

Viewed 50 times

0

Hello, I have a table similar to:

user_id server_id record date_record    
123 333 3 2020-05-10
343 343 4 2020-05-12
133 333 5 2020-05-15
123 333 10 2020-06-10
343 343 12 2020-05-12
123 333 5 2020-05-15
123 433 50 2020-05-10

In this table, records can be repeated by user_id.

I need SQL command that: - Select the best 25 users (the best 25 records), ignoring repeated data. So in the case of user_id 123, the command should only return the maior registro(maior record), ignoring the previous records with least record.

What do I have:

SELECT user_id, server_id, user_record FROM user_ranking ORDER BY user_record DESC LIMIT 100;

The result I desire:(based on the example)

user_id server_id record date_record  


123 433 50 2020-05-10
343 343 12 2020-05-12
133 333 5 2020-05-15
mysql sql

  • "highest record(highest record)" here’s your answer, use it with group by and will have the desired result. If you have questions, you have several questions about group by here

1 answer

1


To do this, group the values using group by, to delete the repeated ones, for example: 

SELECT user_id, 
       server_id, 
       max(user_record) user_record,
       max(date_record) date_record
  FROM user_ranking 
GROUP BY user_id, 
         server_id
ORDER BY user_record DESC 
LIMIT 100;

Note that, all fields using select or in the order by need to be in the group by or using some aggregation function in the select, as is the case with max(user_record).

See working on http://www.sqlfiddle.com/

  • I still get duplicate data :(

  • With some changes I managed to do what I want, the way was this! Thank you Ricardo.

Browser other questions tagged mysql sql

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