Ordering a selection by difference upvotes/downvotes

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

Ordering a selection by difference upvotes/downvotes

Post by defroster »

Hello. I would really appreciate some help. Thanks!

Background:
This is a table containing photos. Now I would like to do a query where I order the result by 'upvotes(up)' minus(-) 'downvotes(down)' .

Code: Select all

Table 'photos'
id
cat_id
type_id
title
status
dateposted	
up (int)
down(int)
At the moment I am just able to sort it by the popularity of the 'upvotes' .. see below:

Code: Select all

$sql = "Select photos.*, categories.cat FROM videos, categories
	WHERE photos.cat_id = categories.id AND photos.status ='1' ORDER BY photos.up DESC
	LIMIT $start, $limit";
	$result = mysql_query($sql);

If a photo has more up-votes than down-votes it will be a positive number.
If a photo has more down-votes than up-votes it will be a negative number.

And I would like to sort the query running from the highest positive number to the largest negative number.

I am very puzzled on how to solve this and would very much appreciate some help. Cheers /df
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Ordering a selection by difference upvotes/downvotes

Post by Eran »

Code: Select all

ORDER BY (photos.up - photos.down) DESC
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

Re: Ordering a selection by difference upvotes/downvotes

Post by defroster »

Thank you so much. For some reason I thought it would be mega complicated. You made my day.
Post Reply