Help..
Moderator: General Moderators
Help..
I have two tables, one table contains a list of users, and a list of sites.
Information you should know:
Users:
member_id - int
points - int
Sites
disabled - int
member_id - int
last_show - int -- Unix time.
site_url - varchar(64)
I want to select a site, where the member_id has the lowest maximum last_show.
For example
SELECT s.* FROM users AS u LEFT JOIN sites AS s ON (s.member_id=u.member_id) WHERE u.points>=1 AND s.disabled=0 GROUP BY member_id ORDER BY max(s.last_show), s.last_show LIMIT 0,1
But, this doesn't work..
Sorry, i posted this in an existing topic, didn't notice tilll later.
Information you should know:
Users:
member_id - int
points - int
Sites
disabled - int
member_id - int
last_show - int -- Unix time.
site_url - varchar(64)
I want to select a site, where the member_id has the lowest maximum last_show.
For example
SELECT s.* FROM users AS u LEFT JOIN sites AS s ON (s.member_id=u.member_id) WHERE u.points>=1 AND s.disabled=0 GROUP BY member_id ORDER BY max(s.last_show), s.last_show LIMIT 0,1
But, this doesn't work..
Sorry, i posted this in an existing topic, didn't notice tilll later.
- hob_goblin
- Forum Regular
- Posts: 978
- Joined: Sun Apr 28, 2002 9:53 pm
- Contact:
- hob_goblin
- Forum Regular
- Posts: 978
- Joined: Sun Apr 28, 2002 9:53 pm
- Contact:
I think he wants a temp. table like
and gets an 'aggregation function not allowed'-error
my db is down right now, can't test it - so no proposals at this time
Code: Select all
user # | last seen on
--------+---------------
1 | site A
2 | site C
3 | site Amy db is down right now, can't test it - so no proposals at this time
Sites:
Now, I wish to select site_id 1, because member_id 1 has the lowest max(last_show), and i want the lowest last_show for that user.
Code: Select all
|--------------------------------------------|
| site_id | member_id | site_url | last_show |
|--------------------------------------------|
| 1 | 1 | aa | 42 |
| 2 | 1 | ab | 52 |
| 3 | 2 | ba | 41 |
| 4 | 2 | bb | 54 |
|--------------------------------------------|- hob_goblin
- Forum Regular
- Posts: 978
- Joined: Sun Apr 28, 2002 9:53 pm
- Contact:
maybe http://www.mysql.com/documentation/mysq ... -group-row helps you
- hob_goblin
- Forum Regular
- Posts: 978
- Joined: Sun Apr 28, 2002 9:53 pm
- Contact:
Code: Select all
SELECT s.*, MAX(last_show) AS MaxLast FROM sites AS s LEFT JOIN users AS u ON (u.member_id=s.member_id) WHERE s.disabled=0 AND u.points>=0 AND u.member_id!=".intval($member_id)." GROUP BY s.member_id ORDER BY MaxLast, s.last_show LIMIT 0,1