CODEWITHSUNDEEP
mysqlsqldatabase
RNK
RNK

Reputation: 5792

Order by with mathematical formula in mysql

I want to display users' posts on index page with most popular.

And I am calculating it with (likes+views-dislikes)/(today's date-posteddate)

Can I pass mathematical formula in order by clause in mysql?

EDIT:

ok here is the query which I am using right now:

SELECT Posts.PostId, Posts.PostTitle, Posts.TextContent,Posts.PostType, PostedAs, CONCAT(PostedDate,' ',PostedTime) AS Date,
COUNT(PostLikes.PostId) AS Likes, COUNT(PostViews.PostId) AS Views,

(CASE Posts.PostType 
WHEN 'media'
THEN (SELECT MediaContent FROM PostsMedia WHERE PostsMedia.PostId = Posts.PostId GROUP BY PostsMedia.PostId)
ELSE
'non-media'
END
) AS MediaContent,

(CASE Posts.PostType 
 WHEN 'media'
 THEN (SELECT MediaType FROM PostsMedia WHERE PostsMedia.PostId = Posts.PostId GROUP BY PostsMedia.PostId)
ELSE
'non-media'
END
) AS MediaType   
FROM Posts, PostLikes, PostViews 
WHERE Posts.PostId = PostLikes.PostId AND PostLikes.Liked = 1 AND Posts.Classification <> 'sales_related' AND (LOWER(Posts.PostType) != 'text' AND LOWER(Posts.PostType) != 'shout')
AND Posts.Filter<>'HOME' GROUP BY PostLikes.PostId, PostViews.PostId ORDER BY Likes DESC, Views DESC $limitClause

Upvotes: 0

Views: 360

Answers (1)

Digital Chris
Digital Chris

Reputation: 6202

Yes. You didn't provide any real code, but to approximate it:

SELECT (likes+views-dislikes)/(today's date-posteddate) AS popularity
FROM thetable
ORDER BY popularity DESC

or 

SELECT item1, item2
FROM thetable
ORDER BY (likes+views-dislikes)/(today's date-posteddate) DESC

Upvotes: 3

Related Questions