CODEWITHSUNDEEP
mysqlsqlselectmin
Hunter Zhao
Hunter Zhao

Reputation: 4649

MySQL MIN() function returns only one record

I have a dataset as per:

+----+---------------+-------+-------------+---------------------+
| id | ip            | port  | point_count | create_time         |
+----+---------------+-------+-------------+---------------------+
|  1 | 192.168.20.28 | 10000 |           0 | 2013-03-29 14:29:14 |
|  2 | 0.0.0.0       | 10000 |           0 | 2013-03-29 14:29:32 |
|  3 | 0.0.0.1       | 11111 |           2 | 2013-03-29 14:29:38 |
|  4 | 0.0.0.5       | 11112 |           3 | 2013-03-29 14:29:44 |
+----+---------------+-------+-------------+---------------------+
4 rows in set (0.00 sec)

Now, I use mysql's MIN() function to fetch records as per:

mysql> SELECT s.id, s.ip, s.port, MIN(s.point_count) FROM origin_server s;
+----+---------------+-------+--------------------+
| id | ip            | port  | MIN(s.point_count) |
+----+---------------+-------+--------------------+
|  1 | 192.168.20.28 | 10000 |                  0 |
+----+---------------+-------+--------------------+
1 row in set (0.00 sec)

Obviously, there are two rows have the same value for column point_count , but it returned me only one record. I just wanna confirm if this situation is correct. Thanks in advance :)

Upvotes: 2

Views: 5149

Answers (4)

user166390
user166390

Reputation:

Yes, it is correct that only one record is returned: the other values returned are "ill-defined" and cannot be reliably viewed as part of a aggregate!

If an aggregate function is used then it effectively treats the entire query as a single group (barring any other defined grouping). To get well-defined behavior then all the columns in the select list must be aggregates or must be mentioned in a GROUP BY clause.

Using SELECT s.ip, MIN(s.point_count) FROM origin_server s GROUP by s.ip would return 4 records (with the MIN being applied per group), because it defines a different grouping.

Using SELECT MIN(s.point_count) FROM origin_server s would return only the minimum point_count (over the entire query), which is logically correct and is the only information from the original query that is guaranteed. When viewing it as such, it makes sense that only a single record is returned.

Upvotes: 2

Bohemian
Bohemian

Reputation: 425033

It's hard to know what you want, but try this:

select
   ip,
   port,
   min(point_count)
from origin_server

This gives you the minimum value of point_count for each unique combination of ip and port.

If you want the ip and port with the minimum point_count, try this:

select
   ip,
   port,
   point_count
from origin_server
order by 3
limit 1

Upvotes: 1

Dork Programmer
Dork Programmer

Reputation: 79

MIN() function is an agregate function. So it was correct if your code only return 1 line of result

Upvotes: 0

John Woo
John Woo

Reputation: 263723

The reason why you are getting only one record is because MIN() is an aggregate function which return one record for every group. Since you have not specify a GROUP BY clause, the result is normal which gives you only one record.

You can use a subquery to get the minimum value of point_count and equate it to the outer query's point_count.

SELECT  *
FROM    origin_server 
WHERE   point_count = (SELECT MIN(point_count) FROM origin_server)

OUTPUT

╔════╦═══════════════╦═══════╦═════════════╦═════════════════════╗
║ ID ║      IP       ║ PORT  ║ POINT_COUNT ║     CREATE_TIME     ║
╠════╬═══════════════╬═══════╬═════════════╬═════════════════════╣
║  1 ║ 192.168.20.28 ║ 10000 ║           0 ║ 2013-03-29 14:29:14 ║
║  2 ║ 0.0.0.0       ║ 10000 ║           0 ║ 2013-03-29 14:29:32 ║
╚════╩═══════════════╩═══════╩═════════════╩═════════════════════╝

Upvotes: 6

Related Questions