CODEWITHSUNDEEP
mysql
Todd
Todd

Reputation: 97

Problem using Min(field)

The problem was between the keyboard and the seat folks.  
Thanks for lending your knowledge!

have these two queries:

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 minimum function. There is only one record in my table 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 the "Min" to "Max" it returns the highest (and only) startdate (20100505).

Why doesn't MySQL like the Min function I'm using?

Some additional info: classroomid is int(11) and startdate is bigInt(20)

Upvotes: 2

Views: 774

Answers (3)

Mark Byers
Mark Byers

Reputation: 838256

Please double-check that there is in fact only one row with classroomid = 6. I would suspect that there is more than one row.

Notice that your first query will always return at most one row because of the GROUP BY, even if there is more than one matching row in the database. The correct way to count the number of rows with classroomid = 6 is as follows:

SELECT COUNT(*)
FROM unitTemplates
WHERE classroomid = 6

Please note in particular that there is no GROUP BY clause in this query.

I tried recreating the situation you described and was unable to - 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 in a test database to convince yourself that it works as expected.

Upvotes: 1

Dolph
Dolph

Reputation: 50650

This is sort of a hack, but maybe limit the results to non-zero values?

WHERE startdate NOT NULL AND startdate > 0

Are you sure there's not a null or zeroed date in that column?

Upvotes: 0

JYelton
JYelton

Reputation: 36512

I'm not sure how the date column is formatted, but you might want to use an order by clause instead. If the date is sequential, 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 the single row for classroomid 6 with the max date. Change "DESC" to "ASC" and you'll get the min date.

Upvotes: 0

Related Questions