获取每个分组中前10名

Tags: /MySQL/ Date Created:

Selecting the one maximum row from each group

mysql - Get top n records for each group of grouped results

SQL: selecting top N records per group

There's a better way: select the variety from each type where the variety is no more than the second-cheapest of that type.

select type, variety, price

from fruits

where (

select count(*) from fruits as f

where f.type = fruits.type and f.price <= fruits.price <--- 这里要注意条件修改！

) <= 2; <-- 这里就是 Top N设定

The simply, fast, quick way to get the Top N records of each group in MySQL:

For example:

Get Top 5 App name which consume traffic volme of each day:

SELECT `day`, app_name, traffic, rank

FROM

(

-- Assign rank for each group rows

SELECT @n := IF(@prev <> `day`, 1, @n + 1) AS "rank", @prev := `day`, `day`, app_name, traffic

FROM

( -- normal queries

SELECT `day`, app_name, SUM(rx_total) "traffic" FROM traffic WHERE `day` > 20160701 GROUP BY `day`, app_name ORDER BY `day`, traffic DESC

) t1,

(SELECT @prev="", @n=0) t2

) A

WHERE rank <=5