Re: group by & order by rand() problem

[Michael Stassen]

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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]