RE: group by issue...??

[bruce]

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:paul@stripped]
Sent: Friday, September 10, 2004 4:36 PM
To: bedouglas@stripped; mysql@stripped
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/mysql?unsub=1