Hi.
Note, that when you do a GROUP BY, according to ANSI SQL you may
SELECT only the columns grouped by and any grouping functions (like
SUM), i.e. selecting PACK_INC would result in an error.
MySQL extends ANSI SQL in that it allows to select abitrary columns,
but chooses a random value from the group in question, which can be
quite handy in some situations. I.e. it can even happen, that you get
back '1' once and '2' the other time. This feature mainly comes to
use, if either you know that all values in that column are the same,
or if you just want an abitrary value as example.
Btw, the same applies to MAST.CODE and TAB.SIZE.
AFAIK, there is no clean way to do what you want in just one
query. There exists a hack, but better is a work-around with two
queries. You can look both up in the fine manual, also the standard
SQL way of solving this (which won't work with MySQL yet):
http://www.mysql.com/doc/e/x/example-Maximum-column-group-row.html
Bye,
Benjamin.
On Sat, Mar 31, 2001 at 09:01:01AM -0600, [EMAIL PROTECTED] wrote:
[...]
> +------------------------+-----------+----------+----------+------+-------+
> | CODE | ADVRT_NBR | ADVRT_ID | PACK_INC | SIZE | QUEUE |
> +------------------------+-----------+----------+----------+------+-------+
> | INSERT LATER CODE FOR 1| 10000 | 2 | 1 | 60 | 2 |
> | INSERT LATER CODE FOR 2| 10001 | 2 | 1 | 100 | 3 |
> | INSERT LATER CODE FOR 2| 10001 | 2 | 2 | 100 | 12 |
> +------------------------+-----------+----------+----------+------+-------+
> 3 rows in set (0.05 sec)
>
> The keys are ADVRT_NBR, ADVRT_ID,PACK_INC.
>
> I'm trying to find the maximum QUEUE value and use the keys for further
> processing. This is the statement that I developed:
>
>
> mysql> SELECT MAST.CODE
> -> ,MAST.ADVRT_NBR
> -> ,MAST.ADVRT_ID
> -> ,TAB.PACK_INC
> -> ,TAB.SIZE
> -> ,MAX(TAB.QUEUE) AS MAX
> -> FROM ADVRT_MAST AS MAST
> -> INNER JOIN LEVEL_3 AS TAB
> -> ON TAB.ADVRT_NBR = MAST.ADVRT_NBR
> -> AND TAB.ADVRT_ID = MAST.ADVRT_ID
> -> WHERE MAST.TYPE = 'S'
> -> AND MAST.ADVRT_STATUS = 'A'
> -> GROUP BY MAST.ADVRT_NBR
> -> ,MAST.ADVRT_ID
> -> ORDER BY MAX DESC;
>
> +------------------------+-----------+----------+----------+------+------+
> | CODE | ADVRT_NBR | ADVRT_ID | PACK_INC | SIZE | MAX |
> +------------------------+-----------+----------+----------+------+------+
> | INSERT LATER CODE FOR 2| 10001 | 2 | 1 | 100 | 12 |
> | INSERT LATER CODE FOR 1| 10000 | 2 | 1 | 60 | 2 |
> +------------------------+-----------+----------+----------+------+------+
>
> The results set is incorrect because the PACK_INC is not correct (well,
> according to my expected results), it should be a "2" and not a "1" for the
> maximum queue value. I understand that I am grouping by the first two keys
> (ADVRT_NBR,ADVRT_ID) but how do I get the correct PACK_INC based off of the
> maximum QUEUE?
>
> Another item is that I AM still interested in the next highest QUEUE
> value....
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
