CODEWITHSUNDEEP
mysqlsql
KILL3RTACO
KILL3RTACO

Reputation: 85

SQL - Use Value of Column Name as Paramter

I have a SQL query to get the amount of 'likes' a user has. There are two different tables, one table holds different user data (not the amount of likes). The other table holds the like logs such as who gave the like, who's receiving the like, when it was liked etc. I'm create a top ten list of the most liked users.

What I'm wondering is if it's possible to use the current value of the column as a parameter while the query is being run, like so:

SELECT `username` FROM `users` ORDER BY (SELECT COUNT(*) FROM `likes` WHERE `liked_user`=?) LIMIT 10

Where the question mark would be replaced with something that represents the current value of username (I assume SQL has to do some kind of loop to get its results)

The alternative I have is having a column in the user table holding the amount of likes, then sorting the based on that, but I was wondering if a query could perform the same job by looking at the like logs.

Upvotes: 0

Views: 64

Answers (1)

MarcinJuraszek
MarcinJuraszek

Reputation: 125610

Yes, you can do that. Should be as easy as:

SELECT u.`username`
FROM `users` u
ORDER BY (SELECT COUNT(*)
          FROM `likes` l
          WHERE l.`liked_user` = u.`username`)
LIMIT 10

You can also do the same without subquery:

SELECT u.`username`
FROM `users` u
JOIN `likes` l ON l.`liked_user` = u.`username`
GROUP BY u.`username`
ORDER BY COUNT(*)

Upvotes: 2

Related Questions