My database has a main table ('grants') with grant listings. Each grant has one listing, including a grantid column.
A second table ('dates') holds date info about each grant. 'grants' to 'dates' is one-to-many as a grant can have several submission dates. The tables are related by the grantid. This table also has a yearday column for sorting. (Some of the grants don't have years attached as they are ongoing.)
Here is a sample SELECT statement that's not working the way I want it to.
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. If the listings are already in ascending order, it works but if they are stored in descending order, my sort can be off. It works the way it's supposed to when I remove the GROUP BY clause but I get multiple listings for each grant.
I hope this is clear enough. I'm guessing there's a better way to do this that works the way I want it to.
Thanks. -- 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]
