Say I have the following table (TBL):
KEY GRP VAL
1 A 2
2 A 3
3 A 1
4 B 2
5 B 1
6 B 3
"select KEY, max(VAL) from TBL group by GRP" gives:
KEY max(VAL)
------------
1 3
4 3
the desired result though is:
KEY max(VAL)
------------
2 3
6 3
any ideas on how to achieve this ?
Apparently this query is invalid in other RDBMs (like
Oracle) which don't allow it. The rule is that one should
not have columns in your select that are not in the group
by section, except for the grouping functions.
MySQL though does allow it, but results can be strange as
above. I realise that some selects don't make sense eg:
"select KEY, max(VAL), min(VAL) from TBL group by GRP"
(which KEY should be returned ?)
I suppose one possibilty for solving my problem would be:
1. select GRP, max(VAL) from TBL group by GRP
2. for each row in 1.:
select KEY from TBL where GRP=? and VAL=?
Any other ideas or comments ?
regards Jurgen
__________________________________________________________________________
http://www.webmail.co.za/dialup Webmail ISP - Cool Connection, Cool Price
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
