On Sun, Mar 16, 2003 at 08:02:02AM +0000, Daniel Harik wrote:
> Hello,
>
> Guys i try to join to tables
>
> slides:
> id
> userid
> file
> moment
>
> users
> id
> username
>
> As there few slids per user and i want to get only last one, i use following
> sql query, but it fetches me first slide. How can i make it fetch last one
> please?
>
> SELECT slides.file, slides.moment, users.id, users.username FROM slides,
> users where users.id=slides.userid GROUP BY users.id desc
This isn't a PHP question. This relates to the DBMS. You should specify
which DBMS you are using. However, MySQL is the only DBMS I know of that
will let you run that query, so I'll assume that you're using MySQL. I'll
also assume that the Moment column is a time. If it is not, replace
Moment with whichever column identifies the last slide.
SELECT u1.UserName, u1.UserId, s1.File, s1.Moment
FROM Users u1, Users u2, Slides s1, Slides s2
WHERE u1.UserId = s1.UserId
AND u2.UserId = s2.UserId
AND u1.UserId = u2.UserId
GROUP BY u1.UserId, s1.SlideId
HAVING Max(s1.Moment) = Max(s2.Moment);
BTW, you can't assume that your original statement will always return the
first slide. People who have tested GROUP BY statements say that the
value returned from columns with no aggregate function is somewhat
random.
Bob Hall
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
