How to choose records with ties in SQL?

909 Views Asked by At

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.

2

There are 2 best solutions below

1
On

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.

2
On

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.