CODEWITHSUNDEEP
phpsql
Owen
Owen

Reputation: 431

Highest rating between two columns in sql select

Say I have the following table:

--------------------
|ID|Yes|No|Filename|
|01|15 |10|1.php   |
|02|12 |5 |2.php   |
|03|6  |1 |3.php   |
--------------------

What I want to do is apply Yes/Yes+No in an sql select statement and echo the highest value's filename.

The Yes and No are updated from time to time, so would it be better to create another column to handle the equation, or can an equation be used in a sql statment? And what would the select statment look like for this?

Thanks

Upvotes: 1

Views: 153

Answers (2)

John Woo
John Woo

Reputation: 263943

for MySQL,

SELECT  Filename, 
        CAST((Yes / (Yes + No)) AS DECIMAL(10,5)) rating
FROM    table1
WHERE   CAST((Yes / (Yes + No)) AS DECIMAL(10,5)) =
        (
            SELECT MAX(rating)
            FROM
            (
                SELECT CAST((Yes / (Yes + No)) AS DECIMAL(10,5)) rating
                FROM table1
            ) x
        )

SQLFiddle Demo

for SQL Server

SELECT   TOP 1 WITH TIES Filename, 
         (Yes / (Yes + No)) rating
FROM     table1
ORDER BY rating DESC

SQLFiddle Demo

Upvotes: 6

Adam Wenger
Adam Wenger

Reputation: 17580

You can use this approach:

SELECT TOP 1 sf.Filename
   , (sf.Yes / (sf.Yes + sf.No)) AS RatioValue
FROM dbo.storeFile AS sf
ORDER BY (sf.Yes / (sf.Yes + sf.No)) DESC

Upvotes: 2

Related Questions