1

Suppose I get this table:

MyTable
+----+-------+
| ID | count |
+----+-------+
| a  | 2     |
| b  | 6     |
| c  | 4     |
| d  | 6     |
| e  | 2     |
+----+-------+

Now I want this back:

Result
+----+-------+
| ID | count |
+----+-------+
| b  | 6     |
| d  | 6     |
+----+-------+

I want the IDs that have the most counted values. So if there are multiple maximum values, I want them all. Also I don't know if there will be multiple values and if there are, how many it will be.

QuantumHive
  • 5,613
  • 4
  • 33
  • 55
  • Looks like similar: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column – Marc Apr 11 '14 at 07:25
  • Just for curiosity: why is this question downvoted? It seems to be a valid question. Is it because no tries are posted from the OP? Or because there are already similar posts and answers at StackExchange? – wumpz Apr 11 '14 at 07:40
  • @Marc That one does indeed look similar, but it doesn't answer my question. I already stumbled upon that one before posting my question. – QuantumHive Apr 11 '14 at 07:50
  • @wumpz Believe me I tried, I'm struggling with this since yesterday. Else I wouldn't have posted it. – QuantumHive Apr 11 '14 at 07:51

7 Answers7

5

You can get the greatest value inside the subquery. Eg,

SELECT  *
FROM    MyTable
WHERE   count = 
        (
            SELECT  MAX(count) 
            FROM    MyTable
        )
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • This answer is the most straightforward and the most elegant one. – QuantumHive Apr 11 '14 at 07:48
  • @QuantumHive [*Join vs. sub-query*](http://stackoverflow.com/questions/2577174/join-vs-sub-query) this will give you the better idea but performance can be measured by explain plan of query – M Khalid Junaid Apr 11 '14 at 08:12
1
SELECT
  Id, count
FROM MyTable
WHERE count = (SELECT MAX(count) FROM MyTable)
Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68
0
select * from MyTable where count in (select max(count) from MyTable)
hkutluay
  • 6,794
  • 2
  • 33
  • 53
0
select * from mytable 
where 
  count= (select max(count) from mytable )
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
0

Try this

select ID, count from table where count in (
    select distinct count from table order by Value desc limit 1
) order by count desc

You requested only top row but by this query if you want top 3 you easily can change it to limit 3

Reza
  • 18,865
  • 13
  • 88
  • 163
0

You can use join instead of using subquery 

SELECT *,t.max_count 
FROM Table1 t1
JOIN (SELECT MAX(`count`) max_count FROM Table1 ) t
HAVING t1.`count`=t.max_count

Fiddle Demo

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
0

Try this:

select id, max(count) as max_count 
  from table1 where count=(select max(count) from table1)
group by id

http://sqlfiddle.com/#!2/abb0b/9

wumpz
  • 8,257
  • 3
  • 30
  • 25