select MAX(date),value from MY_TBL where date < '2002-3-2' group by id

This result doesn't return me anything! Which would lead me to the same conclusion above. mysql groups everything first, decides the maximum value(in this case '2002-3-2' and then applies the where clause and since '2002-3-2' is not less than the date i was looking for it doesn't report anything!

Is their a way to change the order of operations so that is first evaluates the where clause and then does the group/max?

A way using a single query to get the value at a maximum date?

Thanks for the help.

Sincerely,Andrew Mirskyajmirsky@hotmail.com

MattR
—
2002-03-22T02:03:01Z —
#2

According to the SQL standard you cannot include columns in your SELECT which are not in the group by. It shouldn't (and doesn't in this case) work.

Try grouping by DATE instead.

Adding 'value' to the SELECT clause will cause problems -- what if you had the following data:2002-2-2 zero2002-2-2 first

In relational algebra you must always have distinct tuples (rows) in your result set. No exceptions. All tables must have, in some fashion, a unique way of identifying them. Remember that a result-set is just another mathematical set.

So, when you ask the RDBMS to GROUP BY col1 it is going to whine "Well, you have this col2 thing hanging around". So you can try it in most RDBMS's (and it should break). However, MySQL 'relaxed' the standards a bit to make it more useful (Sybase did as well).

In the above example the GROUP BY and SUM wants to return a single row, e.g.

SELECT sum( col1 ), col1
FROM table1
GROUP BY col1

Is logical:

result set
sum, col1
----------
3, 1
4, 2
6, 3

However, since you're not grouping by COL2 in the example, you make the engine go "Wait, you're asking me to duplicate sum counts, right? Uhm, ok. I want to return a single tuple for sum, but you're telling me that I can't. So, I can do one of two things. Return a single tuple for sum and randomly pick a col2 to display, or for each col2 which is in the sum, repeat the sum." In MySQL and Sybase, the latter is chosen:

Explaining why relational calculus says this is VERBOTEN is outside the scope of this post; check out Codd's papers or get a relational calculus/algebra book.

MattR
—
2002-03-22T04:48:25Z —
#5

The standards also say that GROUP BY can only be used with one or more aggregate functions (but not all).

For example:

SELECT sum( col1 )
FROM table1
GROUP BY col1

Could return duplicate values (never mind the results would be fairly meaningless ), so you need to include 'col1' in the SELECT.

Conversely, you cannot have a GROUP BY without aggregates. Again this comes to relational calculus restrictions but MySQL/Sybase say 'Well, we'll group them pretty for you' even though NEIN say Codd and Date.

Sybase has a handy feature that you can enable when you run queries to let you know when you're deviating from the SQL standard:

1> SET FIPSFLAGGER ON
2> GO
1> SELECT SUM( col1 ), col2
2> FROM table1
3> GROUP BY col1
4> GO
Line number 1 contains Non-ANSI text. The error is caused due to the use of
column name(s) in a select list with aggregates but column name(s) not in group
by list.
1> SELECT username
2> FROM user
3> GROUP BY username
4> GO
FIPS WARNING: GROUP BY clause specified when all the items in select list are
aggregate functions or none of the items in select list are aggregate
functions.

DR_LaRRY_PEpPeR
—
2002-03-22T05:26:23Z —
#6

:weyes: :looko: :wall:

that was WAY too much information for me. thanks though. very informative, as usual. i understand that what i thought is wrong now.