min(column) is not returning me correct data of ot

问题:

Below is what I have

+++++++++++++++++++++++++++
id + myDate       + name
+++++++++++++++++++++++++++
'A'+ '2012-06-05' + 'baz'
'A'+ '2012-06-04' + 'bar'
'B'+ '2012-06-05' + 'foo'
'C'+ '2012-06-05' + 'bla'
'C'+ '2012-06-04' + 'blah'
'C'+ '2012-06-06' + 'bleh'
+++++++++++++++++++++++++++

Query I am using is

SELECT id, min(myDate) as Date, name 
FROM myTable
GROUP BY id;

I am getting output as below

+++++++++++++++++++++++++++
id + myDate       + name
+++++++++++++++++++++++++++
'A'+ '2012-06-04' + 'baz'
'B'+ '2012-06-05' + 'foo'
'C'+ '2012-06-04' + 'bla'
+++++++++++++++++++++++++++

---

My question is when query is giving me correct date, why data of column name is coming as baz instead of bar as name for date 2012-06-04 is bar.

Demo to test data.

回答1:

You grab

1. the id,
2. the lowest date for that id, and
3. any of the names that go with it.

Other (normal) databases won't even allow this query. They would force you to either use an aggregate function for name, or add name to the group by as well. MySQL instead picks a random value, and there's your problem.

To solve this, your query will become slighly more complex:

select
  t.id, 
  t.mydate,
  t.name
from
  myTable t
where
  t.mydate = 
    (select 
      min(td.mydate) 
    from 
      myTable td 
    where 
      td.id = t.id)

Or:

select
  t.id, 
  t.mydate as date,
  t.name
from
  myTable t
  inner join 
    (select 
      td.id
      min(td.mydate) as date
    from 
      myTable td 
    group by 
      td.id) tx on tx.id = t.id and tx.date = t.mydate

回答2:

You have to use a different query, as was pointed by DevArt :

SELECT m.* from myTable m join
(select id, min(myDate) md from myTable group by id) mm
on m.id=mm.id and m.myDate=md

Your existing query don't specify a constraint on the name column, that's the reason why you don't get the result you were expecting. Group by only makes a set from which you can take values, using functions like min, or randomly if you don't specify anything as you did.

回答3:

For your specific question, All u need to do is apply the MIN function to a concatenated field:

SELECT id, min(CONCAT(myDate,' ++ ',name)) as date_name 
FROM myTable
GROUP BY id;

and process it in your server-side script

---

You are using a GROPU BY. Which means each row in result represents a group of values. One of those values is the group name (the value of the field you grouped by). The rest are arbitrary values from within that group.
For example the following table:

F1   |   F2
1         aa
1         bb
1         cc
2         gg
2         hh

If u will group by F1: SELECT F1,F2 from T GROUP BY F1 You will get two rows:

1    and one value from (aa,bb,cc)
2    and one value from (gg,hh)

If u want a deterministic result set, you need to tell the software what algorithem to apply to the group. Several for example:

1. MIN
2. MAX
3. COUNT
4. SUM

etc etc

In your case, u did not apply a deterministic algorithem to the name field, so u get a "random" result from the set.