Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is another example of a TOP X records per Y example. For every question, you want 4 answers. A LIMIT is actually needed TWICE... First to limit the qualifying questions, and another "ranking" of answers which guarantees the "Correct" answer to ALWAYS be included per question result set.</p> <p>So my approach is to apply the random against questions first to get that as a subset result, then join that to the answers and limit X per Y. THEN, we can get it all wrapped up. The critical thing here is the inner query has to be ordered by the question ID... AND the qualifier the "Correct" answer is always in first position, but anything after is randomized to include a total of 4 records.</p> <p>Then, the final query applies the WHERE clause to only include where the ranking sequence is &lt;= 4 (of the possible all 9 answers included for 1 question, but then applies a final "ORDER BY" clause to keep the questions together, but randomizes the answers so the "Correct" is no longer always returned in the first position. You can remove this outer "ORDER BY" clause for testing purposes just to confirm functionality, then add it back in later.</p> <pre><code>select FinalQA.* from ( select QWithAllAnswers.*, @RankSeq := if( @LastQuestion = QWithAllAnswers.id, @RankSeq +1, 1 ) ARankSeq, @LastQuestion := QWithAllAnswers.id as ignoreIt from ( SELECT q.id, q.question, q.RandQuestionResult, a.question_id, a.answer, a.correct FROM ( SELECT q.ID, q.Question, q.question_ID, RAND() as RandQuestionResult FROM questions q WHERE q.subject_id = 18 ORDER BY RAND() LIMIT 5) JustQ JOIN answers a on q.id = a.question_id ORDER BY JustQ.RandQuestionResult, if( a.correct = 1,0.000000, RAND() ) QWithAllAnswers, ( select @RankSeq := 0, @LastQuestion := 0 ) SQLVars ) FinalQA where FinalQA.ARankSeq &lt; 5 order by FinalQA.RandQuestionResult, rand() </code></pre> <p>Couple small changes... Make sure at the <code>SQLVars</code> has <code>:=</code> for each of the assignments. When I originally posted, I left one ":" off which could have thrown a false error. I also qualified the inner "Order by" by using "a.correct = 1" (had no alias reference). Finally, changed the outer WHERE clause to just <code>&lt; 5</code> instead of <code>&lt;= 4</code>. I've done MANY of these greatest X per Y groupings and know they work, just missing something simple I'm sure.</p> <p>Also, adjusted the <code>IF()</code> random to have first value as a decimal, otherwise all randoms get set to 1 (whole number) and never fraction... Also for possible issues of when the ORDERING is applied, I've pre-queried all Q and A pre-sorted to get all Correct answers in the first position, THEN apply the <code>SQLVars</code> against that set, then finalize the rank sequence and ordering.</p>
    singulars
    1. This table or related slice is empty.
    1. POmysql complicated sql
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJay D
    1. USDRapp
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POmysql complicated sql
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI tried your suggestion and got the following error... #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 'QWithAllAnswers where QWithAllAnswers.ARankSeq <= 4 order by Q' at line 27
      singulars
      1. PO
      1. USSteveF
    2. CO@user1292262, see comments of revised answer and sql adjustments.
      singulars
      1. PO
      1. USDRapp
    3. COI fixed some typos and built your solution here: http://www.sqlfiddle.com/#!2/34906/52. I know what example you modeled off of, and I had considered the same one. But looks like it suffers from the exact same problems I had in my own solution I made and posted as a comment to OP. It still doesn't randomize the answers for some odd reason, but just grabs the first four answers from each question. So bizarre. Maybe it's a problem just with sqlfiddle.com or the version of MySQL used there? I don't have a MySQL instance myself available to test on...
      singulars
      1. PO
      1. USmellamokb
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload