Re: Tricky math SQL statement
| From: | Hugh Bothwell | Date: | Sun, 20 Apr 2003 14:25:05 +0000 |
| Subject: | Re: Tricky math SQL statement | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-27194@lists.php.net to get a copy of this message | ||
"Merlin" <news.groups@web.de> wrote in message
news:20030420081645.66500.qmail@pb1.pair.com...
> Hi there,
>
> I have a tricky SQL statement which I would like to put on a MySQL 3.X DB
I have no experience with 3.x; I can tell you
how I would do it on 4.x and you will have to
adapt it as needed.
> tablelayout:
> timestamp user_id points
>
> The goal is to get out of this table the top 3 users which have acchieved
> the most points in the last 7 days.
SELECT user_id, SUM(points) AS total
FROM table
GROUP BY user_id
WHERE timestamp > date_sub(NOW(), INTERVAL 7 DAYS)
ORDER BY total DESC
LIMIT 3