Reputation: 431
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
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
)
for SQL Server
SELECT TOP 1 WITH TIES Filename,
(Yes / (Yes + No)) rating
FROM table1
ORDER BY rating DESC
Upvotes: 6
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