sorry... still couldn't see how to apply that particular page to my question...
still trying to get an answer/solution.. -bruce -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 2:18 PM To: [EMAIL PROTECTED] Cc: 'gerald_clark'; [EMAIL PROTECTED] Subject: Re: date question.. The manual describes 3 solutions to this problem <http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html>. Michael bruce wrote: > the earlier suggestion didn't work, as it only returned a single value... > > here's the entire issue i have... > > i have a select: > mysql> select > -> u1.urltype as type, > -> p1.name as fname, > -> p1.fileID as ID, > -> h1.itemID as hitem, > -> h1.process as process, > -> h1.status as status, > -> h1.tblType as tbl, > -> h1.date as date > -> from university_urlTBL as u1 > -> left join parsefileTBL as p1 > -> on p1.university_urlID = u1.ID > -> left join historyTBL as h1 > -> on h1.itemID = p1.fileID > -> where h1.tblType = '3' > -> and (u1.urltype = '3' or urltype = '4') > -> and u1.universityID='40'; > > which returns: > +------+-------+------+-------+---------+--------+------+------------------- > --+ > | type | fname | ID | hitem | process | status | tbl | date > | > +------+-------+------+-------+---------+--------+------+------------------- > --+ > | 3 | | 159 | 159 | 1 | 0 | 3 | 2004-09-11 > 12:23:15 | > | 3 | | 159 | 159 | 1 | 1 | 3 | 2004-09-11 > 12:25:15 | > | 4 | | 160 | 160 | 1 | 0 | 3 | 2004-09-11 > 12:23:15 | > +------+-------+------+-------+---------+--------+------+------------------- > --+ > 3 rows in set (0.11 sec) > > > i'd like to figure out how to get the query to return the row for the > tbl/type based on the latest/earliest date... > > so for the latest date, the query would return: > +------+-------+------+-------+---------+--------+------+------------------- > --+ > | type | fname | ID | hitem | process | status | tbl | date > | > +------+-------+------+-------+---------+--------+------+------------------- > --+ > | 3 | | 159 | 159 | 1 | 1 | 3 | 2004-09-11 > 12:25:15 | > | 4 | | 160 | 160 | 1 | 0 | 3 | 2004-09-11 > 12:23:15 | > +------+-------+------+-------+---------+--------+------+------------------- > --+ > > i was thinking that a subselect might work, but couldn't get it to work... > > thanks for any pointers/comments.... > > regards, > > -bruce > > > > -----Original Message----- > From: gerald_clark [mailto:[EMAIL PROTECTED] > Sent: Friday, September 10, 2004 11:12 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: date question.. > > > > > bruce wrote: > > >>i have a tbl with the following... >> >>foo date1 >>foo2 date2 >>etc... >> >>how can i do a select to get the last or 1st row based on the >>(latest/earliest) date? >> > > SELECT * FROM tbl order by datecolumn DESC LIMIT 1; > SELECT * FROM tbl order by datecolumn ASC LIMIT 1; > > >>can't seem to get it to work.. >> >>thanks >> >>-bruce >> >> >> >> >> > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
