Hello all,
I'm stumped with the following issue and I don't know how to resolve it (I
think that sentence is just rhetoric)....
I have the following SQL Statement and results:
mysql> SELECT MAST.CODE
-> ,MAST.ADVRT_NBR
-> ,MAST.ADVRT_ID
-> ,TAB.PACK_INC
-> ,TAB.SIZE
-> ,TAB.QUEUE
-> 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';
+------------------------+-----------+----------+----------+------+-------+
| 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....
Any help would be greatly appreciated...
Scott
---------------------------------------------------------------------
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
