MySQL MIN() function returns only one record

Question

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 returne…

Accepted Answer

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)
```

- SQLFiddle Demo

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 ║
╚════╩═══════════════╩═══════╩═════════════╩═════════════════════╝
```

MySQL MIN() function returns only one record

Solution

Problem