Problem using Min (field)
The problem was between the keyboard and the seat folks.
Thanks for lending your knowledge!
have the following two requests:
SELECT classroomid AS crid, startdate AS msd
FROM unitTemplates where classroomid = 6
GROUP BY classroomid
and
SELECT classroomid AS crid, Min(startdate) AS msd
FROM unitTemplates where classroomid = 6
GROUP BY classroomid
The second query uses the minimal function. My table only has one record with a classroomid of 6. The first query returns msd = 20100505, the second query returns msd = 0 (instead of the expected 20100505). If I change "Min" to "Max" it will return the highest (and only) start date (20100505).
Why is MySQL not being used as the Min function I'm using?
More information: classroomid is int (11) and startdate is bigInt (20)
a source to share
Please double check that there is actually only one line with classroomid = 6
. I suspect there is more than one line.
Please note that your first query will always return at most one row due GROUP BY
, even if there are multiple matching rows in the database. The correct way to count the number of lines with is classroomid = 6
as follows:
SELECT COUNT(*)
FROM unitTemplates
WHERE classroomid = 6
Please note in particular that there is no offer in this request GROUP BY
.
I tried to recreate the situation you described and failed - for me MySQL works exactly as expected:
CREATE TABLE unitTemplates (classroomid int(11), startdate bigInt(20));
INSERT INTO unitTemplates values (6, 20100505);
SELECT classroomid AS crid, Min(startdate) AS msd
FROM unitTemplates where classroomid = 6
GROUP BY classroomid;
Result:
6, 20100505
You can try running these commands yourself on a test database to convince yourself that it works as expected.
a source to share
I'm not sure how the date column is formatted, but you can use the order by clause instead. If the date is consistent you can use this query:
SELECT classroomid AS crid, startdate AS msd
FROM unitTemplates
WHERE classroomid = 6
ORDER BY startdate DESC LIMIT 1;
This returns a single row for classroomid 6 with a maximum date. Change "DESC" to "ASC" and you get the minimum date.
a source to share