Distinct Name with date Order by date

Here's my issue. I'm tracking product interest so to speak. Each
time a user clicks on a product the userid, product id, and date is
tracked. Now users usually look at their products of interest several
times. So everytime they click the same product it is tracked as
well.

So as an administrator I want to look at the product to see who's
checked it out and how many times (ie interest level).

Here's what I have now
A.A. - Last Access: 11-12-2007 Total: 2
B.B. - Last Access: 11-26-2007 Total: 2

I can onlly get distinct user and order by name. Then while looping
get the last access date.

But I want the most recent person whos looked at the product to be on
top and don't want see A.A... multiple times.

Anyone have any ideas?

Re: Distinct Name with date Order by date

This is kind of a SQL query question but I'm coding in PHP.

Re: Distinct Name with date Order by date

rojelio@gmail.com wrote in news:2364d92d-52ac-422e-a298-32b22b00bdc2
@b40g2000prf.googlegroups.com:

> This is kind of a SQL query question but I'm coding in PHP.
>

Try comp.databases.mysql

Re: Distinct Name with date Order by date

On Wed, 28 Nov 2007 20:40:01 +0100, wrote:

> Here's my issue. I'm tracking product interest so to speak. Each
> time a user clicks on a product the userid, product id, and date is
> tracked. Now users usually look at their products of interest several
> times. So everytime they click the same product it is tracked as
> well.
>
> So as an administrator I want to look at the product to see who's
> checked it out and how many times (ie interest level).
>
> Here's what I have now
> A.A. - Last Access: 11-12-2007 Total: 2
> B.B. - Last Access: 11-26-2007 Total: 2
>
> I can onlly get distinct user and order by name. Then while looping
> get the last access date.
>
> But I want the most recent person whos looked at the product to be on
> top and don't want see A.A... multiple times.

Sure, prefectly possible in just one query.

comp.databases.
or possibly:
alt.php.sql
(allthough the latter is not in my list, so I won't see it)
--
Rik Wasmus

Re: Distinct Name with date Order by date

rojelio wrote:

> I can onlly get distinct user and order by name. Then while looping get
> the last access date.

SELECT DISTINCT is normally a sign that you're doing something wrong.

What's wrong with:

SELECT
user_id,
product_id,
MAX(access_date) AS last_access,
COUNT(*) AS count_access
FROM product_accesses
GROUP BY user_id, product_id

> But I want the most recent person whos looked at the product to be on
> top

SELECT *
FROM (
SELECT
user_id,
product_id,
MAX(access_date) AS last_access,
COUNT(*) AS count_access
FROM product_accesses
GROUP BY user_id, product_id
) AS sub
ORDER BY sub.product_id, sub.last_access

Easy.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 4 days, 16:00.]
[Now Playing: Badly Drawn Boy - A Peak You Reach]

Sharing Music with Apple iTunes
http://tobyinkster.co.uk/blog/2007/11/28/itunes-sharing/

Re: Distinct Name with date Order by date

Toby A Inkster wrote:

> ORDER BY sub.product_id, sub.last_access

ORDER BY sub.product_id, sub.last_access DESC

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 4 days, 16:28.]
[Now Playing: Train - Rat]

Sharing Music with Apple iTunes
http://tobyinkster.co.uk/blog/2007/11/28/itunes-sharing/

Re: Distinct Name with date Order by date

On Thu, 29 Nov 2007 10:17:15 +0100, Toby A Inkster
wrote:

> rojelio wrote:
>
>> I can onlly get distinct user and order by name. Then while looping get
>> the last access date.
>
> SELECT DISTINCT is normally a sign that you're doing something wrong.
>
> What's wrong with:
>
> SELECT
> user_id,
> product_id,
> MAX(access_date) AS last_access,
> COUNT(*) AS count_access
> FROM product_accesses
> GROUP BY user_id, product_id
>
>> But I want the most recent person whos looked at the product to be on
>> top
>
> SELECT *
> FROM (
> SELECT
> user_id,
> product_id,
> MAX(access_date) AS last_access,
> COUNT(*) AS count_access
> FROM product_accesses
> GROUP BY user_id, product_id
> ) AS sub
> ORDER BY sub.product_id, sub.last_access

(LEFT) JOINS are usually faster (and more portable).
--
Rik Wasmus

Re: Distinct Name with date Order by date

Rik Wasmus wrote:

> (LEFT) JOINS are usually faster (and more portable).

If I understand the structure correctly, then the table consists of
basically these columns:

user_id
product_id
access_date

With perhaps some other columns (IP address, User-Agent, etc) but we're
ignoring them for now. Multiple accesses to a product by a single user are
recorded as multiple rows in the table. If that is indeed the case, then I
don't see how the requested information (for each product, a list of users
sorted in order of last access time, and indicating a count of accesses)
can be produced without using a subquery.

Joins will of course come in handy too, as you probably don't just want to
include the user's ID and product ID, but want to include their names and
perhaps some other info too:

SELECT
p.product_id,
p.product_name,
u.user_id,
u.fullname,
u.email_address,
sub.last_access,
sub.count_access
FROM (
SELECT
user_id,
product_id,
MAX(access_date) AS last_access,
COUNT(*) AS count_access
FROM product_accesses
GROUP BY user_id, product_id
) AS sub
LEFT JOIN products p
ON sub.product_id=p.product_id
LEFT JOIN users u
ON sub.user_id=u.user_id
ORDER BY sub.product_id, sub.last_access DESC

Yes, joins are fast, but if the information can't be extracted in a single
query with joins, then using a subquery or two is still likely to be
significantly faster than just pulling all the data into PHP and doing the
processing there, or (heaven forbid!) pulling off part of the data, and
then performing a second query for each row of the original dataset!

And regarding portability, any database that doesn't support subqueries
belongs on a scrapheap. (Or if it's got its original packaging in good
condition, maybe a museum of antiques and curiosities.)

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 4 days, 19:45.]
[Now Playing: Ryan Adams - Love Is Hell]

Sharing Music with Apple iTunes
http://tobyinkster.co.uk/blog/2007/11/28/itunes-sharing/

Re: Distinct Name with date Order by date

Toby A Inkster wrote:
> Rik Wasmus wrote:
>
>> (LEFT) JOINS are usually faster (and more portable).
>
> If I understand the structure correctly, then the table consists of
> basically these columns:
>
> user_id
> product_id
> access_date
>
> With perhaps some other columns (IP address, User-Agent, etc) but we're
> ignoring them for now. Multiple accesses to a product by a single user are
> recorded as multiple rows in the table. If that is indeed the case, then I
> don't see how the requested information (for each product, a list of users
> sorted in order of last access time, and indicating a count of accesses)
> can be produced without using a subquery.
>
> Joins will of course come in handy too, as you probably don't just want to
> include the user's ID and product ID, but want to include their names and
> perhaps some other info too:
>
> SELECT
> p.product_id,
> p.product_name,
> u.user_id,
> u.fullname,
> u.email_address,
> sub.last_access,
> sub.count_access
> FROM (
> SELECT
> user_id,
> product_id,
> MAX(access_date) AS last_access,
> COUNT(*) AS count_access
> FROM product_accesses
> GROUP BY user_id, product_id
> ) AS sub
> LEFT JOIN products p
> ON sub.product_id=p.product_id
> LEFT JOIN users u
> ON sub.user_id=u.user_id
> ORDER BY sub.product_id, sub.last_access DESC
>
> Yes, joins are fast, but if the information can't be extracted in a single
> query with joins, then using a subquery or two is still likely to be
> significantly faster than just pulling all the data into PHP and doing the
> processing there, or (heaven forbid!) pulling off part of the data, and
> then performing a second query for each row of the original dataset!
>
> And regarding portability, any database that doesn't support subqueries
> belongs on a scrapheap. (Or if it's got its original packaging in good
> condition, maybe a museum of antiques and curiosities.)
>

comp.databases.mysql has a lot on replacing subqueries with JOINs.
Pretty much any subquery can be replaced with a JOIN.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Distinct Name with date Order by date

On Nov 29, 7:16 am, Toby A Inkster
wrote:
> Rik Wasmus wrote:
> > (LEFT) JOINS are usually faster (and more portable).
>
> If I understand the structure correctly, then the table consists of
> basically these columns:
>
> user_id
> product_id
> access_date
>
> With perhaps some other columns (IP address, User-Agent, etc) but we're
> ignoring them for now. Multiple accesses to a product by a single user are
> recorded as multiple rows in the table. If that is indeed the case, then I
> don't see how the requested information (for each product, a list of users
> sorted in order of last access time, and indicating a count of accesses)
> can be produced without using a subquery.
>
> Joins will of course come in handy too, as you probably don't just want to
> include the user's ID and product ID, but want to include their names and
> perhaps some other info too:
>
> SELECT
> p.product_id,
> p.product_name,
> u.user_id,
> u.fullname,
> u.email_address,
> sub.last_access,
> sub.count_access
> FROM (
> SELECT
> user_id,
> product_id,
> MAX(access_date) AS last_access,
> COUNT(*) AS count_access
> FROM product_accesses
> GROUP BY user_id, product_id
> ) AS sub
> LEFT JOIN products p
> ON sub.product_id=p.product_id
> LEFT JOIN users u
> ON sub.user_id=u.user_id
> ORDER BY sub.product_id, sub.last_access DESC
>
> Yes, joins are fast, but if the information can't be extracted in a single
> query with joins, then using a subquery or two is still likely to be
> significantly faster than just pulling all the data into PHP and doing the
> processing there, or (heaven forbid!) pulling off part of the data, and
> then performing a second query for each row of the original dataset!
>
> And regarding portability, any database that doesn't support subqueries
> belongs on a scrapheap. (Or if it's got its original packaging in good
> condition, maybe a museum of antiques and curiosities.)
>
> --
> Toby A Inkster BSc (Hons) ARCS
> [Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
> [OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 4 days, 19:45.]
> [Now Playing: Ryan Adams - Love Is Hell]
>
> Sharing Music with Apple iTunes
> http://tobyinkster.co.uk/blog/2007/11/28/itunes-sharing/



Thanks

Toby you're a cornucopia of help.