I think got it now !!! If you run this query multiple times, you will get a list of colors all the colors and corresponding IDs ordered by Color, and randomly ordered within color. Here is the query:
select AAA.color,AAA.bid id from (select AA.color,AA.id,AA.bid from (select min(A.id) id,B.id bid,A.color from color_table A,color_table B where A.color=B.color and A.id<=B.id group by A.color,B.id) AA order by 1,2,rand()) AAA ; If you add a WHERE clause to the AAA alias and specify a color, the query will indeed give you a random ID for the given color each time you run it. Here is that query: select AAA.color,AAA.bid id from (select AA.color,AA.id,AA.bid from (select min(A.id) id,B.id bid,A.color from color_table A,color_table B where A.color=B.color and A.id<=B.id group by A.color,B.id) AA order by 1,2,rand()) AAA where AAA.color='red' LIMIT 1 ; Just change your table name and you are all set. If you have the table structure, send it and I'll make the query if you want. ----- Original Message ----- From: Rolando Edwards <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Cc: Brian Dunning <[EMAIL PROTECTED]>, Chris Boget <[EMAIL PROTECTED]>, Dwalu Z. Khasu <[EMAIL PROTECTED]> Sent: Thursday, December 14, 2006 12:34:05 PM GMT-0500 US/Eastern Subject: Re: Workaround for distinct? This works also select min(A.id) id,A.color from color_table A left join color_table B on A.color=B.color group by A.color order by rand(); ----- Original Message ----- From: Rolando Edwards <[EMAIL PROTECTED]> To: Dwalu Z. Khasu <[EMAIL PROTECTED]> Cc: Brian Dunning <[EMAIL PROTECTED]>, mysql@lists.mysql.com, Chris Boget <[EMAIL PROTECTED]> Sent: Thursday, December 14, 2006 12:30:02 PM GMT-0500 US/Eastern Subject: Re: Workaround for distinct? Here onoe that should work. It only uses a LEFT JOIN It does not use Subqueries It does not use DISTINCT Here it is : select min(A.id) id,A.color from color_table A left join color_table B on A.color=B.color and A.id<>B.id group by A.color order by rand(); Give it a Try !!! ----- Original Message ----- From: Dwalu Z. Khasu <[EMAIL PROTECTED]> To: Chris Boget <[EMAIL PROTECTED]> Cc: Rolando Edwards <[EMAIL PROTECTED]>, Brian Dunning <[EMAIL PROTECTED]>, mysql@lists.mysql.com Sent: Thursday, December 14, 2006 12:17:30 PM GMT-0500 US/Eastern Subject: Re: Workaround for distinct? On Thu, 14 Dec 2006, Chris Boget wrote: =>>I tried this out this morning on MySQL 5. =>> It works. Please try this in MySQL 4 and see. => =>Unless I'm way off, I do not believe your solution will work in 4.x because =>it doesn't support sub-queries... => 4.1 does. See http://dev.mysql.com/doc/refman/4.1/en/subqueries.html -- - Dwalu .peace -- I am an important person in this world - Now is the most important time in my life - My mistakes are my best teachers - So I will be fearless. - Student Creed -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
