选择按频率排序的顶级不同结果 [英] Select top distinct results ordered by frequency

问题描述

我的表有两列:id 和 name.数据如下所示:

My table has two columns: id and name. The data looks like this:

id | name
----------
1    Jeff
2    Sam
3    Carl
4    Sam
5    Carl
6    Jeff
7    Dave
8    Jeff

我想获得的是:

name | frequency
----------------
Jeff      3
Carl      2
Sam       2
Dave      1

本质上，我需要一个 SQL 查询来计算表中的唯一名称，并按频率对它们进行排序.如果重要的话，我正在使用 MySQL.

Essentially, I need an SQL query that counts the unique names within the table, and sorts them by their frequnecy. I'm using MySQL if it matters.

谢谢.

推荐答案

我没有测试过，所以语法可能不完美，但是像这样的东西呢:

I haven't tested it, so the syntax might not be perfect, but what about something like this :

select name, count(*) as frequency
from your_table
group by name
order by count(*) desc

应该给你唯一的名字和每个名字在表格中出现的相应次数，按那个数字排序.

Should give you unique names and the corresponding number of times each name appears in the table, ordered by that number.

这篇关于选择按频率排序的顶级不同结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！