paul.... forgive me for being a neophyte!!! but i have no idea how what you said helps me get to how i can actually get a grouping....
is there anyway to get a grouping... is there some other way to accomplish this..? am i totally lost!???? thanks.. ps. i'm really trying to figure out how to get the last record from a select/join without having to go through multiple queries/selects... > 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: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 4:36 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: group by issue...?? At 16:27 -0700 9/10/04, bruce wrote: >hi... > >if i do this... > >select >h1.itemID as hitem, >h1.process as process, >h1.status as status, >h1.tblType as tbl, >h1.date as date >from historyTBL as h1 > where (h1.tblType = '3' or h1.tblType = '4'); > >i get a results table with ~5000 rows... > >if i add the 'group by' sql, > >select >h1.itemID as hitem, >h1.process as process, >h1.status as status, >h1.tblType as tbl, >h1.date as date >from historyTBL as h1 > where (h1.tblType = '3' or h1.tblType = '4') >group by tblType; <<<<<<<<<<<<<<<< > >i get a single line for each tblType... > >i was under the impression that i should get the same number of rows >returned, but that they would be grouped together..... > >can someone provide an explanation as to how this should work, and what's >going on???? If you use GROUP BY with selecting the value of any aggregate functions, you achieve the same effect as SELECT DISTINCT. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
