Search code examples
sqlalgorithmgoogle-app-enginejdoql

In SQL or GQL or JDOQL, how can I query for the row that has the highest value in 2 columns (that have the lowest difference)?

Say for example I have a table:

Table: Message
| data | likes | dislikes |

How can I efficiently find the row that has the highest number of likes and dislikes i.e. most controversial. So for the following example: 

{("Hello", 10, 5)
("Ola!", 5, 5)
("Ni Hao!", 2, 2)
("Wazzup!", 5, 7)}

"Hello" would be chosen.

Any help would be highly appreciated!

Solution

  • 1) You might want to use some other metrics instead of abs(likes - dislikes). In this case, (0, 5) and (100, 105) will be equally controversial.
    I think, likes*dislikes might work in your conditions.

    2) I'm not sure about jdoql, but since you specified sql tag, in standard sql this can be done without sorting. Something like 

    select * from Message 
    where likes*dislikes = (select max(likes*dislikes) from Message)