嗨,我有下表,我想选择每月插入的最大值(计数(*)) . sqlfiddle.com/#!2/13036/1
select * from broadcast
profile, plugged, company, tstamp
1, 2, 1, 2013-10-01 08:20:00
1, 3, 1, 2013-10-01 08:20:00
2, 1, 1, 2013-10-01 08:20:00
2, 3, 1, 2013-10-01 08:20:00
3, 1, 1, 2013-10-01 08:20:00
3, 1, 1, 2013-09-01 08:20:00
所以,如果我做了类似以下的事情:
select plugged,
count(*),
extract(month from tstamp),
extract(year from tstamp)
from broadcast
where company=1
group by plugged,
extract(month from tstamp),
extract(year from tstamp)
order by count(*) desc;
输出:
plugged, count(*), extract(month from tstamp), extract(year from tstamp)
3, 2, 10, 2013
1, 2, 10, 2013
2, 1, 10, 2013
1, 1, 9, 2013
期望的输出:
plugged, count(*), extract(month from tstamp), extract(year from tstamp)
3, 2, 10, 2013
1, 2, 10, 2013
1, 1, 9, 2013
这是正确的...但我只想要max(count(*))(例如在这种情况下只有第一行) . 可能存在2行具有最大计数的情况,但是对于每个MONTH / YEAR,我只想返回最大计数行...我是否需要内部选择语句或其他内容?
1 回答
试试这个