atom feed6 messages in com.mysql.lists.mysqlRe: group by & order by rand() problem
FromSent OnAttachments
ale...@capri.itMar 31, 2004 12:56 pm 
Dathan Vance PattishallMar 31, 2004 1:15 pm 
ale...@capri.itMar 31, 2004 1:25 pm 
m.pheasantMar 31, 2004 3:25 pm 
Michael StassenApr 1, 2004 11:56 am 
Alessandro AstaritaApr 2, 2004 1:19 am 
Subject:Re: group by & order by rand() problem
From:Michael Stassen (Mich@verizon.net)
Date:Apr 1, 2004 11:56:52 am
List:com.mysql.lists.mysql

Right. You're grouping by user_id and throwing in title, and you're hoping to influence which of the titles is chosen to go with user_id, but as title is neither part of your group nor part of an aggregate function, its value is undefined. See the manual for an explanation <http://www.mysql.com/doc/en/GROUP-BY-hidden-fields.html>.

You could probably accomplish this with a variant of the MAX-CONCAT trick <http://www.mysql.com/doc/en/example-Maximum-column-group-row.html>. Something like:

SELECT user_id, SUBSTRING(MAX(CONCAT(TRUNCATE(RAND(),4),title)),7) AS Title FROM banners GROUP BY user_id;

Michael

m.pheasant wrote:

Order by is working after the group stage. You would need an aggregate function which chooses a random row. Some other SQL implementations would not let you select a column that is not also grouped (eg title) or in an aggregate function as in your select ... group by ... example.

m

-----Original Message----- From: ale@capri.it [mailto:ale@capri.it]

I have this table:

mysql> select * from banners; +----+---------+---------------+ | id | user_id | title | +----+---------+---------------+ | 1 | 1 | first banner | | 2 | 1 | second banner | | 3 | 2 | third banner | | 4 | 2 | forth banner | | 5 | 2 | fifth banner | +----+---------+---------------+

I would like to show a random banner for each user, something like this:

first call +----+---------+---------------+ | id | user_id | title | +----+---------+---------------+ | 1 | 1 | first banner | | 3 | 2 | third banner | +----+---------+---------------+

second call +----+---------+---------------+ | id | user_id | title | +----+---------+---------------+ | 2 | 1 | second banner | | 4 | 2 | forth banner | +----+---------+---------------+

etc...

I have tried with following query but the banner doesn't change while multiple calls:

SELECT * FROM banners GROUP BY user_id ORDER BY RAND();

Can anyone help me?

Thanks in advance,

Alex