selecting only values having difference greater than 0 (no negatives and 0)

Question

hi!! i've two columns in mysql db table votes : accept and reject

i want to query only the top result after the accept-reject column values

table : votes
=================
 accept | reject
=================
   7    |   9
   5    |   1
   2    |   15
   5    |   1

i want just the positive and top value, here 5-1 = 4 actually, i've lot of other columns along with accept and reject..i just want to ORDER the results by the top value of difference(positive only) and LIMIT it to 1 so that i can get the one row i want..was i clear? :) how to write query for this? thanks..

Answer 1

SELECT (accept - reject) as top_value FROM table WHERE (accept - reject) > 0

So if you have values like this:

=============================
 accept | reject | top_value
=============================
   5    |   1    |   4
   7    |   9    |  -2
   2    |   15   |  -13
   5    |   5    |   0

the query will select only row 1.

Answer 2

I'm assuming those two columns are just an extract from your table, and you want the entire row where (accept-reject) is maximum. Then you can do it like this:

SELECT * FROM votes
WHERE accept-reject = (SELECT MAX(accept-reject) FROM votes)
LIMIT 1

Answer 3

Use:

   SELECT t.accept,
          t.reject,
          t.accept - t.reject AS difference
    FROM VOTES t
   WHERE t.accept - t.reject > 0
ORDER BY difference DESC
   LIMIT 1

Alternate using subquery:

SELECT v.accept,
       v.reject
  FROM VOTES v
 WHERE v.accept - v.reject > 0
  JOIN (SELECT MAX(t.accept - t.reject) AS difference
          FROM VOTES t
         WHERE t.accept - t.reject > 0) x ON x.difference = (v.accept - v.reject)