Re: group by & order by rand() problem

[Alessandro Astarita]

Alle 21:57, giovedì 1 aprile 2004, Michael Stassen ha scritto:
> You could probably accomplish this with a variant of the MAX-CONCAT
> trick
> .
> Something like:
>
>SELECT user_id,
>   SUBSTRING(MAX(CONCAT(TRUNCATE(RAND(),4),title)),7) AS Title
>FROM banners
>GROUP BY user_id;

Thank you so much. This solution works correctly. 

-- 
Alessandro 'Asterix' Astarita <[EMAIL PROTECTED]>
CapriOnLine S.r.l. http://www.caprionline.com/
"Unix IS user friendly. It's just selective about who its friend are"

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

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 
.

You could probably accomplish this with a variant of the MAX-CONCAT trick 
. 
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]

RE: group by & order by rand() problem

[m.pheasant]

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: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 01, 2004 7:16 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: group by & order by rand() problem

Try seeding your rand.

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, March 31, 2004 12:57 PM
> To: [EMAIL PROTECTED]
> Subject: group by & order by rand() problem
> 
> 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]




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



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

RE: group by & order by rand() problem

[[EMAIL PROTECTED]]

> Try seeding your rand.

Tried. It doesn't work. The select shows always the same records but
in different order:

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

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

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

etc...

Alex

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

RE: group by & order by rand() problem

[Dathan Vance Pattishall]

Try seeding your rand.

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, March 31, 2004 12:57 PM
> To: [EMAIL PROTECTED]
> Subject: group by & order by rand() problem
> 
> 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]




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

group by & order by rand() problem

[[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]