I tried this out this morning on MySQL 5.
It works. Please try this in MySQL 4 and see.
drop table if exists color_table;
create table color_table (id int not null auto_increment,color
varchar(10),primary key (id));
--
-- Loading Color Data
--
insert into color_table (color) values
('red'),('blue'),('red'),('yellow'),('yellow'),('blue'),
('green'),('brown'),('green'),('yellow'),('orange'),('blue'),
('pink'),('blue'),('red'),('brown'),('pink'),('blue');
--
-- Full Listing of Table
--
select * from color_table;
--
-- Listing of Colors with the Mininum ID for the Color
--
select min(id),color from color_table group by color;
--
-- Randomizing the Color Listing 5 Times
--
select A.id,A.color from (select min(id) id,color from color_table group by
color) A order by rand();
select A.id,A.color from (select min(id) id,color from color_table group by
color) A order by rand();
select A.id,A.color from (select min(id) id,color from color_table group by
color) A order by rand();
select A.id,A.color from (select min(id) id,color from color_table group by
color) A order by rand();
select A.id,A.color from (select min(id) id,color from color_table group by
color) A order by rand();
Give It A Try !!!
----- Original Message -----
From: Brian Dunning <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Sent: Thursday, December 14, 2006 11:15:39 AM GMT-0500 US/Eastern
Subject: Workaround for distinct?
+----+--------+
| id | color |
+----+--------+
| 1 | red |
| 2 | blue |
| 3 | red |
| 4 | yellow |
| 5 | yellow |
| 6 | blue |
| .. | ... |
I'm trying to select 5 random records, but no more than 1 of any
given color. According to the notes in the documentation and to my
own testing (I'm on v4.x), this doesn't work:
select id,distinct(color) from tablename order by rand();
I found that using 'group by color' works to limit it to one of each
color, but the problem is I always get the same record of each color.
I need to mix it up and give me different random records every time:
select id,color from tablename group by color order by rand();
This should be so easy!! What's the obvious solution that I'm missing?
- Brian
--
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]
