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
