Posted by Rik Wasmus on 11/29/07 11:09

On Thu, 29 Nov 2007 10:17:15 +0100, Toby A Inkster
<usenet200711@tobyinkster.co.uk> 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

[Back to original message]