Thanks Nestor!
I think I am almost there. However, how can I limit the result of a
JOIN in a query, and not the entire result set? For example:
SELECT
*
FROM
a
JOIN
b
ON
a.id = b.id
If I wanted all records from "a" and only the first record from "b", how
would I integrate a LIMIT statement in this? Appending it to the end of
the query will limit the entire result set.
Any ideas?
Thanks again,
Michael
Néstor wrote:
Michael,
Take a look at this link, it talks about limits.
http://72.14.203.104/search?q=cache:8uOO2iKVffAJ:www.oreilly.com/catalog/mysqlckbk/chapter/ch03.pdf+mysql+query+obtain+last+record&hl=en&gl=us&ct=clnk&cd=7&client=firefox-a
<http://72.14.203.104/search?q=cache:8uOO2iKVffAJ:www.oreilly.com/catalog/mysqlckbk/chapter/ch03.pdf+mysql+query+obtain+last+record&hl=en&gl=us&ct=clnk&cd=7&client=firefox-a>
Néstor :-)
On 8/4/06, *Michael Caplan* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
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]>
> <mailto: [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
<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]
