0

I am trying to choose the posts with highest likes or comments from a database. I want to include all options in case of a tie. I tried to use TOP 1 WITH TIES in MYSQL WORKBENCH but it does not give any value. What else can I try? The question goes like - List the post ID with the most number of likes. Display all such posts in case of a tie.

thanks.

crewmate_22
  • 1
  • 1
  • Provide sample data and expected result. – Sujitmohanty30 Sep 22 '20 at 18:38
  • Please add a few rows of sample data and the expected result. It's hard for us to imagine all the details. – The Impaler Sep 22 '20 at 18:38
  • Are you using SQL-Server (from Microsoft) or are you using MySQL (from Oracle)? It's unlikely you're using both. (edit your question and correct the tags.) – MatBailie Sep 22 '20 at 19:28
  • You say you've tried some SQL. Please edit your question and add the sql you tried, then add some example input, the output you actually got, and the outout you want. – MatBailie Sep 22 '20 at 19:29

2 Answers2

1

Information is pretty thin, but it sounds like you should use the rank function, rank allows ties and you can filter on the result of the rank function.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Randall
  • 1,441
  • 13
  • 19
  • The question goes like this List the post ID, content and count of posts with the most number of likes. Display all such posts in case of a tie. I could make a table using sql showing the post ID, content and number of likes. But how can i choose all posts in case of tie? – crewmate_22 Sep 23 '20 at 17:02
  • you write a simple select query, include the rank like in the linked article, after that the simplest is probably turn that query into a sub query, and select all rows where rank = 1 – Randall Sep 24 '20 at 22:33
0

RANK() returns ties with the same rank (e.g., both top comments would have '1') as the output. But if I've understood your specific use case correctly, it might be simpler to use SELECT * FROM (SELECT col, MAX(col) AS top_comment FROM your_table) WHERE col = top_comment . This will return all possible ties for the highest value in the column.

Greg
  • 84
  • 1
  • 1
  • 7
  • So this code is counting the max likes and i need to return the value for the most liked post. In case of tie , I need to return all the posts that have the max number of likes. Would you tell me what should I change here? I tried following what you said but still I am not able to implement it correctly. SELECT Post, Content, COUNT(*) AS NUM_OF_LIKES FROM post INNER JOIN likepost ON Id = Post GROUP BY Post ORDER BY NUM_OF_LIKES desc; – crewmate_22 Sep 23 '20 at 16:58