CODEWITHSUNDEEP
mysqlsqldatabase
Naveh
Naveh

Reputation: 81

Getting table, order by most liked values

I'm using MySQL. I have a table named 'words'

enter image description here

If a user enter a new value 'the word_liked_id' will be 0. If a user likes a value, it will copy the row of the value, and add to 'word_liked_Id' the id of liked value.

My question is, how I write a query which show me the table of regular values (word_liked_id == 0), order by the most liked rows first?

I only know basic MySql, I don't know how to create this "loop needed" action so I don't have a query to share. Thanks for any assistance.

Example of desired output: For example I have 3 rows which the 'word_liked_id' is 93, which means the value in id 93 has 3 likes. I have another 2 rows which the 'word_liked_id' is 45, which means the value in id 45 has 2 likes.

I want to show the table order by the values with the most likes.

Output: So the output will be: row 1: everything in id 93, (Which has the most likes of 3) row 2: everything in id 45, (Which has the second most likes of 2) row 3: others with less or none likes.

Upvotes: 0

Views: 286

Answers (2)

Andrea Colleoni
Andrea Colleoni

Reputation: 6021

You can use window functions.
Window Functions in MySQL

MySQL supports window functions that, for each row from a query, perform a calculation using rows related to that row.

In your case you can use the RANK() or DENSE_RANK() functions.
See: RANK

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can use aggregation and a join:

select w.*, wl.num_likes 
from words w join
     (select wl.word_liked_id, count(*) as num_likes
      from words wl
      group by wl.word_liked_id
     ) wl
     on wl.word_liked_id = w.id
order by wl.num_likes desc;

If you want all rows, even those with no likes, then use left join instead of join.

Upvotes: 1

Related Questions