Distinct Name with Date Order By Date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rojelio@gmail.com - 28 Nov 2007 19:53 GMT

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?

Reply to this Message

Erland Sommarskog - 28 Nov 2007 22:31 GMT

> 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
[quoted text clipped - 14 lines]
> 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.

Signature

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

Reply to this Message

rojelio@gmail.com - 29 Nov 2007 20:20 GMT

I'm going to test out your example erland thanks

btw I'm using SQL Server '05

Reply to this Message