I need an idea how to construct this query.

I have a web page. each time the page is refreshed, I want to select data from a random category, and to select no more than 4 random items from that category.

For example, with different types of music as my categories, and songs from that type of music as my items, three separate refreshes might return:

First refresh:

categoryid     name        title
    212         rap      making it
    212         rap      falling hard
    212         rap      kill it
    212         rap      gone wide

Second refresh:

categoryid     name        title
    200        blues     under you
    200        blues     loving
    200        blues     moving
    200        blues     mine world 

Third refresh:

categoryid     name        title
    312        rock      lazy 
    312        rock      nice 
    312        rock      stoned
    312        rock      my world 

I believe I'm supposed to use a sub-query to return this.

So, the category should be selected at random, the songs within that category should be selected at random (unless there are 4 or fewer songs, in which case they'll all be shown), and the order of the songs should be random.

How would I go about doing this?

  • 1
    This is quite incomprehendable. Please include some sample data, table structure, and expected output showing rows and columns. – danblack Sep 3 '18 at 23:09
  • If I'm following you correctly, you want to return a category at random, and, I'll guess you want to return up to 4 items from the category. What you've included is (I assume) your desired output. We need to know more about the limits on the query. Ultimately, this sounds like a web page, and it's quite possible that some of the restrictions (maybe even including the random category) would be determined there, which would be out-of-scope for this site. – RDFozz Sep 4 '18 at 18:52
  • @RDFozz yes thats what i want to accomplish 4 at least just 4 random values with same catid displaying on random – david Sep 4 '18 at 20:08
  • To clarify further: the 4 selections from the random category should also be randomly selected? Also: "at least 4" would mean there would be cases where more than 4 would be apprpropriate; "just 4" would mean only 4, no matter haw many more than 4 there are. What I suspect you want is "at most 4". If there are only 1, 2, 3, or 4 rows for the category, then return those rows; if there are more than 4 rows, then return 4 randomly selected rows from what's available. – RDFozz Sep 4 '18 at 21:03
  • it wont b more than 4 if there are catid that only got 2 of d same it will return 2 but not more than 4 result cause it will b display on rand not asc or desc – david Sep 4 '18 at 21:22

Will select 4 distinct items at random from that table. You should be able to build both of your requirements based on this pattern.

Each time the query is run, a different 4 will be picked. But... One set of 4 may overlap another set of 4. If this caveat is not to your liking, the code becomes more complex -- you have to remember what you have already displayed, that that involves passing information from one page to the next. That is tricky since HTML is "stateless".

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.