CODEWITHSUNDEEP
mysqlsql
Kyuu
Kyuu

Reputation: 1045

Selecting the top 5 in a column with duplicates

I have written a query that gives me a list of data and I need to select the top 5 pieces of data. For example

Num  Name
5    a
4    b
4    c
2    d
1    e
1    f
1    g
0    h

However if I simply use LIMIT 5, this leaves out data points with name f and g. How would I be able to select from a-h. This is just an example data piece. My actual data contains a lot more rows so I can simply just exclude the bottom row. EDIT sorry when i said top 5 i meant top 5 Num entries. so 5, 4 ,2 ,1,0 but 1 has duplicates so I wanted to select all of these duplicates

Upvotes: 0

Views: 128

Answers (3)

Logan
Logan

Reputation: 1371

You can calculate via adding a new field with an incremental row number within your SQL logic as following: 

Feeds  Num  Name
  1     5    a
  2     4    b
  2     4    c
  3     2    d
  4     1    e
  4     1    f
  4     1    g
  5     0    h

and then limit the result by the required rank (in your case 5). Following is the SQL for your reference: 

SELECT num, name from (
SELECT @row_number:=CASE WHEN @num=num 
THEN @row_number ELSE @row_number+1 END AS feeds,@num:=num AS num, name
FROM table1, (SELECT @row_number:=0,@num:='') AS t
ORDER BY num desc 
  )t1
  WHERE feeds <= 5

SQL-fiddle link

Upvotes: 3

Arun Krish
Arun Krish

Reputation: 2153

Check this query

SELECT 
t1.Num, 
t1.Name,
FIND_IN_SET(t1.Num, SUBSTRING_INDEX(
       GROUP_CONCAT(DISTINCT(t2.Num ) ORDER BY t2.Num DESC), ',', 5)
) AS Ord
FROM yourTable t1
LEFT JOIN yourTable t2 ON(t2.Num IS NOT NULL)
GROUP BY t1.Name
ORDER BY t1.Num ASC

Upvotes: 0

shA.t
shA.t

Reputation: 16968

I think you need a query like this:

SELECT *
FROM (
    SELECT t1.Num, t1.Name, COUNT(DISTINCT t2.Num) AS seq
    FROM yourTable t1
        LEFT JOIN
        yourTable t2
        ON t1.Num <= t2.Num
    GROUP BY t1.Num, t1.Name) dt
WHERE (seq <= 5);

[SQL Fiddle Demo]

Upvotes: 2

Related Questions