[PHP-DB] Re: MySQL: Random select with specific count of a column
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 [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hello Torsten, This is one, but I think you can find another one: Supose your table is called tablename select * from tablename as a left join tablename as b on a.category = b.category and a.nameb.name and a.language=b.language where a.language='de' and b.name is not null group by a.category 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: MySQL: Random select with specific count of a column
Rui Cunha [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: MySQL: Random select with specific count of a column
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 [EMAIL PROTECTED] ha scritto nel messaggio news:[EMAIL PROTECTED] Hi, I've got the following table: categorylanguagename 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
