CODEWITHSUNDEEP
phpmysql
user2045458
user2045458

Reputation: 65

How To get TOP 5 value in SQL with multiple same output that exceed the limit 5

    Database 1           Database 2 
||==============||    ||==============||
||user || value ||    ||user || value ||
||-----||-------||    ||-----||-------||
|| 1   ||  50   ||    || 1   ||  50   ||
|| 2   ||  50   ||    || 2   ||  50   ||
|| 3   ||  50   ||    || 3   ||  40   ||
|| 4   ||  40   ||    || 4   ||  40   ||
|| 5   ||  40   ||    || 5   ||  30   ||
|| 6   ||  40   ||    || 6   ||  20   ||
|| 7   ||  40   ||    || 7   ||  20   ||
|| 8   ||  30   ||    || 8   ||  10   ||
|| 9   ||  30   ||    || 9   ||  10   ||
||==============||    ||==============||

I need to search for Top 5 highest value but instead of only 5 result I need those 7 users with 50 and 40 values.

I cant just disregard they other Top users. If possible to be done in SQL that would be great. if not A help on PHP will do. 

     Result 1              Result 1
||==============||    ||==============||
||user || value ||    ||user || value ||
||-----||-------||    ||-----||-------||
|| 1   ||  50   ||    || 1   ||  50   ||
|| 2   ||  50   ||    || 2   ||  50   ||
|| 3   ||  50   ||    || 3   ||  50   ||
|| 4   ||  40   ||    || 4   ||  50   ||
|| 5   ||  40   ||    || 5   ||  50   ||
|| 6   ||  40   ||    ||==============||
|| 7   ||  40   ||
||==============||

Upvotes: 0

Views: 155

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269913

In general, for a given table, you can do:

SELECT t.*
FROM table t CROSS JOIN
     (SELECT t.*
      FROM table t
      ORDER BY value DESC
      LIMIT 1 OFFSET 4
     ) t5
WHERE t.value >= t5.value;

The subquery chooses the fifth row (OFFSET starts counting at 0 rather than 1). The where clause just chooses anything larger than that value.

If you want to run this on two tables combined, then you would want to use union all.

Upvotes: 1

Barmar
Barmar

Reputation: 781078

SELECT user, value
FROM Database
WHERE value in (
    SELECT value
    FROM Database
    ORDER BY value DESC
    LIMIT 5)
)
ORDER BY value DESC, user

Another way is:

SELECT user, value
FROM Database
WHERE value >= (SELECT MIN(value)
                FROM (SELECT value
                      FROM Database
                      ORDER BY value DESC
                      LIMIT 5) AS x
                )
ORDER BY value DESC, user

Upvotes: 1

Related Questions