CODEWITHSUNDEEP
mysqlsql
tdark
tdark

Reputation: 29

SQL Querying: Getting the top five tuples based on a value where the value could be the same for some tuples

As explained in the title, I am trying to get the top five tuples from a table based on a value that could be the same across some of the tuples. Also, if the fifth tuple's value is the same as other tuples' values in the same table, I need to display them as well. As an example, suppose I have the table with the following values:

I need to display this:

As one can see, despite Persons C-G having the "top five" values, the table also includes Persons A and B because they have the same value as Person C. Also, one can see that executing a query such as

Select * from Person order by Value desc limit 5

would not produce this table as it would exclude Persons A and B. Can anyone provide me with some help regarding this matter? Thank you in advance.

Upvotes: 2

Views: 361

Answers (1)

sgeddes
sgeddes

Reputation: 62861

I assume you mean MySQL and not SQL Server (please edit your tags). If so, you can use a subquery with in to get the ties:

Select *
From person
Where value In (
    Select value
    From person
    Order By value Desc
    Limit 5)
Order by value Desc

Depending on your desired results, you may need to use distinct in your subquery. A little unclear with your sample data.

Alternatively, you could use a join:

Select p.*
From person p
   join (
    Select distinct value
    From person
    Order By value Desc
    Limit 5) p2 on p.value = p2.value
Order by p.value Desc

(I used distinct with this example to show the difference.)

Upvotes: 1

Related Questions