ORDER BY using MATH on TWO COLUMNS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ORDER BY using MATH on TWO COLUMNS

Author

Topic

smcallister
Starting Member

15 Posts

Posted - 2009-04-29 : 09:55:51

I have a table with about 200 entries, including two columns named "thumbsup" and "thumbsdown". I want to sort them based on who has the highest thumbs rating.

Currently: "SELECT ID, name, thumbsup, thumbsdown, (thumbsup - thumbsdown) AS total from listings ORDER BY total DESC"

However, if an entry has 0 thumbsup and 0 thumbsdown, it is listed at the exact same level as an entry with 10 thumbsup and 10 thumbsdown. I want to give those entries with higher "activity" a higher ranking than those entries with none.

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-29 : 10:01:02

"SELECT ID, name, thumbsup, thumbsdown, (thumbsup-thumbsdown) AS total, ((CASE WHEN thumbsup = 0 THEN 0.1 ELSE thumbsup END) - thumbsdown) AS totalOrder from listings ORDER BY totalOrder DESC"

smcallister
Starting Member

15 Posts

Posted - 2009-04-29 : 10:05:31

whitefang = epic win

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-04-29 : 10:16:55

declare @table table (ThumbsUp int,ThumbsDown int)

INSERT INTO @Table
SELECT 10,0 UNION
SELECT 10,10 UNION
SELECT 0,10 UNION
SELECT 0,0 UNION
SELECT 5,5 UNION
SELECT 1,4

SELECT ThumbsUp,ThumbsDown,[Total] = ThumbsUp - ThumbsDown
FROM
@table

ORDER BY Total DESC,ThumbsUp + ThumbsDown Desc

Jim

smcallister
Starting Member

15 Posts

Posted - 2009-04-29 : 10:18:41

ACTUALLY, the code below still sorts places with 0 thumbsup and 0 thumbsdown above those with 1/1

"SELECT ID, name, thumbsup, thumbsdown, (thumbsup-thumbsdown) AS total, ((CASE WHEN thumbsup = 0 THEN 0.1 ELSE thumbsup END) - thumbsdown) AS totalOrder from listings ORDER BY totalOrder DESC"

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-29 : 10:20:24

Yeah, it's because you have to cast it as a decimal. The "ORDER BY Total DESC,ThumbsUp + ThumbsDown Desc" is a better solution.

smcallister
Starting Member

15 Posts

Posted - 2009-04-29 : 10:28:05

THANKS VERY MUCH FOR BOTH YOUR EFFORTS.

Working perfectly now.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-04 : 08:14:10

Or you can include a sorting and ranking algorithm based on
Bayesian estimate, which also weights number of total thumbs up.

weighted rating (WR) = (v ÷ (v+m)) × R + (m ÷ (v+m)) × C

where:
R = average for the movie (mean) = (Rating)
v = number of votes for the movie = (votes)
m = minimum votes required to be listed in the Top list
C = the mean vote across the whole report (currently x.y)

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-04 : 08:15:50

E 12°55'05.63"
N 56°04'39.26"

april198474
Starting Member

11 Posts

Posted - 2009-09-22 : 23:08:59

You can add some logic about these situation before SQL, or the entries with none you can make an exception. Hope this will help you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources