Help..

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

ReDucTor
Forum Commoner
Posts: 90
Joined: Thu Aug 15, 2002 6:13 am

Help..

Post 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.
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post 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
ReDucTor
Forum Commoner
Posts: 90
Joined: Thu Aug 15, 2002 6:13 am

Post 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.
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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 ;)
ReDucTor
Forum Commoner
Posts: 90
Joined: Thu Aug 15, 2002 6:13 am

Post 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.
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

ReDucTor
Forum Commoner
Posts: 90
Joined: Thu Aug 15, 2002 6:13 am

Post 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.
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post by hob_goblin »

.
Last edited by hob_goblin on Sat Oct 12, 2002 2:22 pm, edited 2 times in total.
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post by Coco »

if you are using mysql 4 you could use a subquery that would probably do it for you...
ReDucTor
Forum Commoner
Posts: 90
Joined: Thu Aug 15, 2002 6:13 am

Post 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. :D haha
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

o-m-g :D
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 ;)
ReDucTor
Forum Commoner
Posts: 90
Joined: Thu Aug 15, 2002 6:13 am

Post 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.
ReDucTor
Forum Commoner
Posts: 90
Joined: Thu Aug 15, 2002 6:13 am

Post 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
Post Reply