CODEWITHSUNDEEP
mysqlsql
user2991421
user2991421

Reputation: 417

Find the biggest single number

I have a table number as follows:

num
---
8
8
3
3
1
6
4

I want to find out the biggest number which only appear once. I've this query:

select max(num) from number group by num having count(*)=1

It returns:

{"headers": ["max(num)"], "values": [[1], [4], [6]]}

What is the reason behind this?

Upvotes: 2

Views: 1120

Answers (3)

galloleonardo
galloleonardo

Reputation: 154

You can do so:

SELECT TOP 1 MAX(num)
FROM   number
GROUP BY num 
HAVING COUNT(num) = 1
ORDER BY num DESC

I hope I have helped.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

Using 

 select max(num) from number group by num having count(*)=1

You are using group by num so the max(num) for group by num is like num

so you get each num with count(*) =1

If you need the max you should use 

select max(num) from
( select num
from number 
group by num
having count(*) = 1   ) t

Upvotes: 3

Pradeep Pati
Pradeep Pati

Reputation: 5919

Aggregate functions, such as max() are applied to each group as determined by the group by clause. Since you are grouping the numbers by their value, the query is finding the max in each group, which just happens to be the same number.

To solve this, you could use subqueries, or order in a descending way while limiting the no of rows to 1.

Upvotes: 1

Related Questions