2

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

Improve this question
2
  • What value are you wanting in the min column? The overall min, or the min for each ip/port? Commented Apr 1, 2013 at 2:54
  • I just want only one record that has the minimum point_count :) Commented Apr 1, 2013 at 2:57

4 Answers 4

Reset to default
6

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 ║
╚════╩═══════════════╩═══════╩═════════════╩═════════════════════╝
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

+1 You edited your question as per my request for info around why and how that query worked at all, just as I typed it ;-) So can you explain how mySql chooses which of the two (or more) rows to return as its "group", when no GROUP BY is specified? I presume that's just the first row returned, based on clustered index?
@Sepster you cannot really determine the record that will be selected. It's stated on the manual (I'm searching for the link).
2

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.

Improve this answer

4 Comments

+1. But: "it could have been any of the other values returned". While this is true in the context of the OPs question, is this strictly true in general? eg perhaps it could be the first record found, based on eg clustered index? or is it genuinely undefined behaviour?
@Sepster It depends upon the exact query plan and implementation of such .. or "ill-defined" in my book. That is, for a given query [plan] and database it is likely consistent - add a different index and then it could be a different query plan entirely.
Ah, I see. Yes, agree that's "ill-defined". I'd call it "undefined for all practical purposes". I come from an MSSQL background, where aggregation functions cannot be used within a select containing non-aggregated results, unless each non-aggregated column is also included explicitly in a GROUP BY.
@Sepster Yes, I come from MSSQL and very much prefer the strictness in this aspect. I used "ill-define" here (in quotes) because I'm not technically certain how it is defined (or not) .. I should probably try to track down some better wording or an authoritative resource.
1

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
Improve this answer

4 Comments

I just want only one record that has the minimum point_count :)
OK. See edit to answer for hopefully something more useful. Note that the accepted answer may return multiple rows.
at first I think that if the situation in my question is incorret, I would use LIMIT, thanks for your answer:)
Thank me by up voting :) (up-voting means "it's useful")
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.