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]