The Little Guy Posted March 3, 2009 Share Posted March 3, 2009 I cant get this to work... SELECT DISTINCT * from class_event_entered WHERE rundate='03-03-2009' ORDER BY `class` DESC, ORDER BY RAND() #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY RAND() LIMIT 0, 30' at line 1 I would like to order the selected by class (1+ values), then within that class group order those randomly. Instead I get the above error, how can I do this? Quote Link to comment https://forums.phpfreaks.com/topic/147821-order-group-by-rand/ Share on other sites More sharing options...
The Little Guy Posted March 3, 2009 Author Share Posted March 3, 2009 OMG, I hate when I solve the prob 10 seconds after posting, but I did solve it: SELECT DISTINCT * from class_event_entered WHERE rundate='03-03-2009' ORDER BY `class`, RAND() Quote Link to comment https://forums.phpfreaks.com/topic/147821-order-group-by-rand/#findComment-775879 Share on other sites More sharing options...
The Little Guy Posted March 3, 2009 Author Share Posted March 3, 2009 I changed my mind not solved I would like to add to it... SELECT DISTINCT * from class_event_entered WHERE rundate='03-03-2009' ORDER BY `class` LIMIT 6, RAND() I would like something like that. Say class "A" has 10+ rows (10+ people apart of class "A") I only want to select 6 of those, and do that for each class in the database. How can I do this? I thought the above would work, but it doesn't. So the returned values would look like so: A player1 A player2 A player3 A player4 A player5 A player6 B player7 B player8 B player9 B player10 B player11 B player12 ... Quote Link to comment https://forums.phpfreaks.com/topic/147821-order-group-by-rand/#findComment-775892 Share on other sites More sharing options...
fenway Posted March 4, 2009 Share Posted March 4, 2009 Wait, you want to do what? Quote Link to comment https://forums.phpfreaks.com/topic/147821-order-group-by-rand/#findComment-776187 Share on other sites More sharing options...
The Little Guy Posted March 4, 2009 Author Share Posted March 4, 2009 I want to grab 6 random values per class. So say I have 3 classes with 10 people in each class (30 people) I want to select six random people from each class for a total of 18. That means select 6 people from class a, six people from class b and six people from class c. The example above just uses 3 classes, there could more classes and there could be less classes. A class could even have less than six people in it as well. How would I do that? Quote Link to comment https://forums.phpfreaks.com/topic/147821-order-group-by-rand/#findComment-776596 Share on other sites More sharing options...
fenway Posted March 4, 2009 Share Posted March 4, 2009 Well, if you wanted the "top 6", you could simply self join with a group by & having clause... Quote Link to comment https://forums.phpfreaks.com/topic/147821-order-group-by-rand/#findComment-776679 Share on other sites More sharing options...
The Little Guy Posted March 4, 2009 Author Share Posted March 4, 2009 Well, if you wanted the "top 6", you could simply self join with a group by & having clause... No, I would like a "random 6", unless that is what you mean... Quote Link to comment https://forums.phpfreaks.com/topic/147821-order-group-by-rand/#findComment-776683 Share on other sites More sharing options...
fenway Posted March 4, 2009 Share Posted March 4, 2009 Well, I suppose you could still "number" them somehow and then filter the "rank".... Quote Link to comment https://forums.phpfreaks.com/topic/147821-order-group-by-rand/#findComment-776685 Share on other sites More sharing options...
The Little Guy Posted March 4, 2009 Author Share Posted March 4, 2009 Does MySQL have something like a Foreach? so I could do something like this? ... FOREACH(`class` ORDER BY RAND() LIMIT 6) ... Quote Link to comment https://forums.phpfreaks.com/topic/147821-order-group-by-rand/#findComment-776702 Share on other sites More sharing options...
The Little Guy Posted March 7, 2009 Author Share Posted March 7, 2009 anyone have any Idea? Quote Link to comment https://forums.phpfreaks.com/topic/147821-order-group-by-rand/#findComment-778633 Share on other sites More sharing options...
fenway Posted March 9, 2009 Share Posted March 9, 2009 I gave you the only idea. Quote Link to comment https://forums.phpfreaks.com/topic/147821-order-group-by-rand/#findComment-780141 Share on other sites More sharing options...
The Little Guy Posted March 10, 2009 Author Share Posted March 10, 2009 I gave you the only idea. yeah... but how? Quote Link to comment https://forums.phpfreaks.com/topic/147821-order-group-by-rand/#findComment-781548 Share on other sites More sharing options...
fenway Posted March 13, 2009 Share Posted March 13, 2009 Well, if you wanted the "top 6", you could simply self join with a group by & having clause... I gave you the only idea. yeah... but how? See here. Quote Link to comment https://forums.phpfreaks.com/topic/147821-order-group-by-rand/#findComment-783843 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.