I just noticed that MSAccess and SQL server support FIRST() and LAST()
functions. Is there an equivalent in MySQL? My research has come up
with nil so far.
Thanks,
Michael
Michael Caplan wrote:
Hi there,
I am trying to figure out how to "flatten" the result set of a join
query using aggregate functions. For several fields (b.refering_url,
c.string, b.first_page, b.last_page) I need to pull out the _first_ or
_last_ item as ordered from the records returned from the join.
However, I'm just lost for how this would be accomplished. I'd like
to say FIRST(col), or LAST(col).
Any ideas?
Below is a sample query I have been plugging away at.
Thanks,
Michael
SELECT
a.visitor_id as cookie,
a.ip_address as ipaddress,
b.refering_url as referingdomain,
a.browser_type as browsertype,
a.os as operatingsystem,
MIN(b.first_visit_time) as firsttime,
MAX(b.last_visit_time) as lasttime,
DATE_FORMAT(MIN(b.first_visit_time), \'%H\') as hour,
(DATE_FORMAT(MIN(b.first_visit_time), \'%w\') + 1) as day,
DATE_FORMAT(MIN(b.remote_time), \'%H\') as localhour,
(DATE_FORMAT(MIN(b.remote_time), \'%w\') + 1) as localday,
a.browser_language as language,
c.string as keyword,
a.color_depth as colordepth,
a.res_height_pix as screenheight,
a.res_width_pix as screenwidth,
COUNT(b.visit_id) as visit,
b.first_page as firstpage,
b.last_page as lastpage
FROM
lps_visitor as a
JOIN
lps_visits as b
ON
a.visitor_id = b.visitor_id
LEFT JOIN
lps_keywords as c
ON
c.keyword_id = b.keyword_id
GROUP BY
a.visitor_id,
a.ip_address,
a.browser_type,
a.os,
a.browser_language,
a.color_depth,
a.res_height_pix
ORDER BY
a.visitor_id,
b.visit_id
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
