Posted by Toby A Inkster on 11/29/07 09:17

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/

[Back to original message]