Forming An SQL Query That Selects The Max Difference Of Two Fields

Question

I'm trying to select a record with the most effective votes. Each record has an id, the number of upvotes (int) and the number of downvotes (int) in a MySQL database.

I know basic update, select, insert queries but I'm unsure of how to form a query that looks something like:

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

Answer 1

You are not using MAX() right.

Here is pretty fast query:

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

To run an 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 that on a table with 250,000 records (it's very similar - inventory usage - looking for a most popular part) and it took .203 of a second - on my dev machine - this is not even production server ( where it tppl 0.016 of a second)

UPDATE:

Yeah I didn't think about that possibility that you might have more than one top 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 records as a text field, that can be easily parsed out if you need to.

Answer 2

There may be more than 1 record that match that condition. In order to get them all you could do something like this in mysql:

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