Posted by Erland Sommarskog on 11/28/07 22:31

(rojelio@gmail.com) writes:
> 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.

For these types of queries, it's a good idea to include:

o CREATE TABLE statement for you table.
o INSERT statements with sample data.
o The desired result given the sample.
o Which version of SQL Server you are using.

This helps to clarify your questions, and also makes it possible to
develop a tested solution. Here is an outline query for what I think
you are asking for:

SELECT person, lastview = MAX(viewtime), total = count(*)
FROM productsview
GROUP BY person

But there is quite some guesswork behind it, so it may be completely
off-track.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]