On 28 May 2003 at 14:17, Peter Fleck wrote:
SELECT grants.grantid, grants.refnum, dates.subdaynum FROM grants, dates WHERE (grants.agency = "NIH-O" AND grants.grantid = dates.grantid) GROUP BY grants.grantid ORDER BY dates.yearday;
This gives me one listing per grant but they are not sorted correctly. From what I can figure out, mysql is only looking at the first occurence of a grant in the 'dates' table and uses the yearday from that listing.
Yes, that seems reasonable. Since you're not telling it which
yearday you want, you're saying it doesn't matter which one it uses. In most cases you wouldn't do that unless you expected all the values
of yearday to be the same for a particular value of grantid.
MySQL does its best, but it can't read your mind. You have to tell it what you want. If you want the latest yearday, for example, then select MAX(dates.yearday) AS max_yearday and order by that.
Thanks. That does solve the problem. I use MIN to sort ascending but I get the concept.
GROUP BY is hard for me to understand and the online manual isn't very clear. -- Peter Fleck Webmaster | University of Minnesota Cancer Center Dinnaken Office Bldg. 925 Delaware St. SE Minneapolis, MN 55414 612-625-8668 | [EMAIL PROTECTED] | www.cancer.umn.edu Campus Mail: MMC 806
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]