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 Ho

Solution 1:

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

Feeds  Num  Name
  15a24b24    c
  32    d
  41    e
  41    f
  41    g
  50    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:=CASEWHEN@num=num 
THEN@row_number ELSE@row_number+1ENDAS feeds,@num:=num AS num, name
FROM table1, (SELECT@row_number:=0,@num:='') AS t
ORDERBY num desc 
  )t1
  WHERE feeds <=5

SQL-fiddle link

Solution 2:

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
    GROUPBY t1.Num, t1.Name) dt
WHERE (seq <= 5);

[SQL Fiddle Demo]

Solution 3:

Check this query

SELECT 
t1.Num, 
t1.Name,
FIND_IN_SET(t1.Num, SUBSTRING_INDEX(
       GROUP_CONCAT(DISTINCT(t2.Num ) ORDERBY t2.Num DESC), ',', 5)
) AS Ord
FROM yourTable t1
LEFT JOIN yourTable t2 ON(t2.Num ISNOT NULL)
GROUPBY t1.Name
ORDERBY t1.Num ASC