>I am dumbfounded how to use RAND() in a query statement. > >I have three columns for this example and I want the results randomly >sorted by the invcode column. That is, to randomly display how the >results are grouped. > >select invcode, price, years >from inventory >order by invcode, years DESC, price DESC > >I've tried the retrieve results in random order example in the mySQL >book, page 188, but it says syntax error. > >select invcode, price, RAND() as ran, years >from inventory > >I've also tried HAVING but no luck. This has to be simple but I just >can't find the variant in the syntax. This is a MS Access DB >connected by OLE. I also can't find any examples of the syntax in >Access books (nor on the Allaire site). > >Thank you, > >Randy Zeitman Paul writes: >My guess is that it's a MySQL special function. You might have to create >your own version using CF. > >For example, > ><CFSET STARTROW="#randrange(1,YourQuery.recordcount)#> > >will pick a random row number out of your results (note that the row >numbers go 1,2,3, etc, that is, they are not Table ID numbers). You save >this in a list and repeat (not accepting results already selected) until >you have selected RecordCount values. Shouldn't be too bad. > >best, paul Thank you but I don't think this works...it chooses specific rows while I need to choose random groups. Query Result: row1:Group A row2: a-1 row3: a-2 row4: a-3 row5: Group B row6: b-1 row7: b-2 row8: b-3 row9: Group C row10: c-1 row11: c-2 row12: c-3 I want the query to randomize the groups, A, B, C; B C A, etc... Do I have to do many random queries and mesh them together? Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
