PHP DB - MySQL: Random select with specific count of a column

Author

MySQL: Random select with specific count of a column

Torsten Roehr

2004-06-29, 8:55 am

Hi,

I've got the following table:

category language name
1 de a
1 de b
1 de c
2 de a
2 de b
2 de c
3 de a
3 de b
3 de c
....
1 en a
1 en b
1 en c

I would like to select 6 random rows where the language is 'de' AND make
sure that I will always have 2 rows of EACH category in my result set:
1 de b
1 de c
2 de a
2 de c
3 de a
3 de b

Any help greatly appreciated.

Thanks and best regards,

Torsten Roehr

Rui Cunha

2004-06-30, 8:56 am

Hi Torsten,

until now,the best i could get was getting 6 random rows...still working on
ensuring to retrive just 2 rows for category...meanwhile,you can solve the
problem easily with a php loop trough the following query:

select field_id , language , category
from yourtable
where language = 'de'
group by category , field_id
order by category , rand()
limit 6;

PS1: i'm considering you're using just one table and my table structure is
as follows: yourtable (field_id , language, category).
in case you're using more than 1 table, just add the required joins...

hope this helps.

Rui Cunha

Torsten Roehr writes:

> "Pablo M. Rivas" <pmrivas01@yahoo.com.ar> wrote in message
> news:12572007801.20040629110044@yahoo.com.ar...
>
> Hi Pablo,
>
> thanks for your help. Unfortunately it's always returning the same row for
> each category (maybe because of the group by) and only one row for each
> category. I need to select 2 random rows for each category. Any more ideas?
>
> Thanks, Torsten
>

Torsten Roehr

2004-06-30, 8:56 am

"Rui Cunha" <rui.cunha@ual.pt> wrote in message
news:20040630091042.AFC6D156CE@odin.ual.pt...
> Hi Torsten,
>
> until now,the best i could get was getting 6 random rows...still working
on
> ensuring to retrive just 2 rows for category...meanwhile,you can solve the
> problem easily with a php loop trough the following query:
>
> select field_id , language , category
> from yourtable
> where language = 'de'
> group by category , field_id
> order by category , rand()
> limit 6;
>
> PS1: i'm considering you're using just one table and my table structure is
> as follows: yourtable (field_id , language, category).
> in case you're using more than 1 table, just add the required joins...
>
> hope this helps.

Hi Rui,

thank you very much for your efforts. I guess I have to do seperate queries
(as you suggested) for each category and then join the results.

Thanks again and best regards,

Torsten

Franciccio

2004-06-30, 8:57 pm

I suggest to first analize the problem and then go through the query, php
coding ...etc etc

In the table u have only one superkey which is also a key and it is made of
the three fields (attributes) 'category', 'language' and ' name'. You should
consider to look for a prymary key randomly, toghether with some restriction
in the search. Something like select the pryimary key from the table where
'category' equals a number(1, 2,3,....n) and 'name' equals some random
criterium generated number. A random criteria could be generating a random
variables between the ascii code representing the alphabet letters (from 97
to 122 to cover a,b,c,...z).
You need to do some work before u can get an executable code here.
Hope can open a road
Bye

"Torsten Roehr" <roehr@zilleon.com> ha scritto nel messaggio
news:20040629110054.63882.qmail@pb1.pair.com...
> Hi,
>
> I've got the following table:
>
> category language name
> 1 de a
> 1 de b
> 1 de c
> 2 de a
> 2 de b
> 2 de c
> 3 de a
> 3 de b
> 3 de c
> ...
> 1 en a
> 1 en b
> 1 en c
>
> I would like to select 6 random rows where the language is 'de' AND make
> sure that I will always have 2 rows of EACH category in my result set:
> 1 de b
> 1 de c
> 2 de a
> 2 de c
> 3 de a
> 3 de b
>
> Any help greatly appreciated.
>
>
> Thanks and best regards,
>
> Torsten Roehr

Sponsored Links