Generating an SQL query that selects the maximum difference between the two fields

Question

I am trying to choose a recording with the most effective voices. Each entry has an id, the number of upvotes (int) and the number of downvotes (int) in the MySQL database.

I know the basic update, select, insert requests, but I'm not sure how to form a request that looks something like this:

SELECT * FROM topics WHERE MAX(topic.upvotes - topic.downvotes) 

Answer 1

You are not using MAX () on the right.

Here is a pretty quick request:

 SELECT id, (upvotes - downvotes) AS popular FROM topics ORDER BY popular DESC LIMIT 1 

Run update:

 UPDATE topics, (here you put your select statement from above in parenthesis ) AS popular_query SET topic.description = "I'm popular" WHERE topics.id = popular_query.id 

I just ran this on a table with 250,000 entries (it is very similar - using inventory - finding the most popular part), and it took 0.203 seconds - on my dev machine it is not even a production server (where it is tppl 0.016 seconds)

UPDATE:

Yes, I did not think about this possibility that you might have some better results.

 SELECT GROUP_CONCAT(id) AS popular_ids, (upvotes - downvotes) AS popular FROM topics GROUP BY popular ORDER BY popular DESC LIMIT 1 

popular_ids - will contain popular entries in the form of a text field, which can be easily analyzed if you need.

Answer 2

There may be more than 1 record matching this condition. To get them all, you could do something like this in mysql:

 SELECT * FROM topics WHERE upvotes - downvotes = (select MAX(upvotes - downvotes) from topics) 

Answer 3

  SELECT (upvotes-downvotes) AS NetVotes, * FROM topics ORDER BY NetVotes DESC LIMIT 1 

Answer 4

Does it do this for you?

 SELECT * FROM topics ORDER BY topic.upvotes - topic.downvotes DESC LIMIT 1;