Page 1 of 2
Help..
Posted: Sat Oct 12, 2002 10:17 am
by ReDucTor
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.
Posted: Sat Oct 12, 2002 10:30 am
by hob_goblin
SELECT * FROM sites ORDER BY last_show ASC LIMIT 1;
try that... it might be a little wrong but thats the main idea
Posted: Sat Oct 12, 2002 10:46 am
by ReDucTor
yes, but that selects the site with the lowest last_show, i want to get the site with the lowest last_show where the user the lowest maximum last_show so then it would be fair.
Posted: Sat Oct 12, 2002 1:10 pm
by hob_goblin
ReDucTor wrote:i want to get the site with the lowest last_show where the user the lowest maximum last_show so then it would be fair.
Please restate the question, as it doesn't make sense.
Posted: Sat Oct 12, 2002 1:21 pm
by volka
I think he wants a temp. table like
Code: Select all
user # | last seen on
--------+---------------
1 | site A
2 | site C
3 | site A
and gets an 'aggregation function not allowed'-error
my db is down right now, can't test it - so no proposals at this time

Posted: Sat Oct 12, 2002 1:34 pm
by ReDucTor
Sites:
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 |
|--------------------------------------------|
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.
Posted: Sat Oct 12, 2002 1:51 pm
by hob_goblin
SELECT * FROM sites WHERE member_id = '$id' ORDER BY last_show ASC LIMIT 1;
and just use a loop if you want all of the member_id's
Posted: Sat Oct 12, 2002 1:53 pm
by volka
Posted: Sat Oct 12, 2002 2:00 pm
by ReDucTor
hob, i am wanting to select it mainly with in one query, not select the user first, then have to repeat another query.
Posted: Sat Oct 12, 2002 2:17 pm
by hob_goblin
.
Posted: Sat Oct 12, 2002 2:19 pm
by Coco
if you are using mysql 4 you could use a subquery that would probably do it for you...
Posted: Sat Oct 12, 2002 3:49 pm
by ReDucTor
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
I got it.

haha
Posted: Sun Oct 13, 2002 9:41 am
by volka
o-m-g
If you have the time to perform a benchmark I would like to know wether your query pays compared with two simpler '...where...order by...limit'-querries

Posted: Sun Oct 13, 2002 10:17 pm
by ReDucTor
Well It only fetchs the max once per user. then it fetches the lowest site...because only one site per user gets returned.
Posted: Sun Oct 13, 2002 10:36 pm
by ReDucTor
really, the order by i don't think there is any way to optimise that, because it needs to order them by the max, then get that users lowest