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

(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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Distinct Name with Date Order By Date

I'm going to test out your example erland thanks

btw I'm using SQL Server '05