Most accessed record

[fly904]

I'm trying to return the most accessed record over the past n days. For example: Record one was accessed three times two days ago and record two was accessed twice yesterday. I want it to return record two if the number of days is 1 and record one if the day value (n) is 2 (or over). What I have done so far is below. But it doesn't work the way required. It just returns the most accessed. Sample data is included. details

id (int) [pk]

(Rest of table)

1

...

2

...

views

detail_id (int)[pk]

viewed_on (datetime)[pk]

1

01/01/2010 01:00:00

1

01/01/2010 02:00:00

1

01/01/2010 03:00:00

2

02/01/2010 01:00:00

2

02/01/2010 02:00:00

For the sake of argument, it's 03/01/2010.

SELECT
details.id
FROM
details
WHERE
details.id = (SELECT
views.detail_id
FROM
views
WHERE
(TO_DAYS(NOW()) - TO_DAYS(views.viewed_on)) <= 2
ORDER BY
(SELECT
COUNT(v.detail_id)
FROM
views v
WHERE
v.detail_id = views.detail_id )
DESC LIMIT 1)

Note: UK date. (Like it should be!)

If at first you don't succeed, you're not Chuck Norris.

[chevu]

Hi your query is wrong, the condition for day limits should be checked when you are counting ids. Please recheck your query...

[Arun Immanuel]

SELECT id FROM details WHERE id = (
SELECT A.detail_id FROM (
SELECT detail_id, COUNT(detail_id) view_count FROM views WHERE
(TO_DAYS(NOW()) - TO_DAYS(viewed_on)) <= 2
GROUP BY detail_id ORDER BY view_count DESC LIMIT 1
) A
);

Regards, Arun Kumar.A