Thanks Nestor,
I thought about that, but limit 1 doesn't work in my scenario as I want
to access both the FIRST() and LAST() column for a result set
simultaneously. By telling MySQL to limit to 1, I could get the first,
but not the last. I want mysql to give me the first, drop everything in
between, and the last.
Any ideas?
Thanks,
Michael
Néstor wrote:
I beleive that when you do your query you can add 'limit 1' and the
query will return the
first record only.
Néstor :-)
On 8/4/06, *Michael Caplan* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
