StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I don't know that much about SQL so this is probably not the slickest method but no-one else has contributed so far.</p> <p>Compare each character in each position and rank the result. </p> <p>rank = the amount of characters that are the same in the given comparison string "0101101001" as that in the string of answers in each row, so (N.B. You said 11 in your question but gave 10 digits as an example)</p> <pre><code>0101101001 against 0101101001 gives a rank of 10 0101101001 against 1101101001 gives a rank of 9 0101101001 against 1111111001 gives a rank of 7 0101101001 against 0001100111 gives a rank of 6 </code></pre> <p>As SQL</p> <pre><code>SELECT answers, ( #looking for 0101101001 IF (substring(answers,1,1)=0, 1, 0) + IF (substring(answers,2,1)=1, 1, 0) + IF (substring(answers,3,1)=0, 1, 0) + IF (substring(answers,4,1)=1, 1, 0) + IF (substring(answers,5,1)=1, 1, 0) + IF (substring(answers,6,1)=0, 1, 0) + IF (substring(answers,7,1)=1, 1, 0) + IF (substring(answers,8,1)=0, 1, 0) + IF (substring(answers,9,1)=0, 1, 0) + IF (substring(answers,10,1)=1, 1, 0) ) AS rank FROM yesno ORDER BY ( #looking for 0101101001 IF (substring(answers,1,1)=0, 1, 0) + IF (substring(answers,2,1)=1, 1, 0) + IF (substring(answers,3,1)=0, 1, 0) + IF (substring(answers,4,1)=1, 1, 0) + IF (substring(answers,5,1)=1, 1, 0) + IF (substring(answers,6,1)=0, 1, 0) + IF (substring(answers,7,1)=1, 1, 0) + IF (substring(answers,8,1)=0, 1, 0) + IF (substring(answers,9,1)=0, 1, 0) + IF (substring(answers,10,1)=1, 1, 0) ) DESC </code></pre> <p>As PHP</p> <pre><code>function getAnswers($lookslike) { /* expects "binary" string returns the answers and rank (0 to string length) ordered by closest match first */ foreach (str_split($lookslike) as $i=>$bit){ $ifs[]='IF (substring(answers,' . ($i+1) . ',1)=' . $bit . ', 1, 0) '; } // use your db class return $db->select_many(' SELECT answers, ( '. implode(' + ', $ifs) .' ) AS rank FROM yesno ORDER BY ( '. implode(' + ', $ifs) .' ) DESC '); } </code></pre> <p><strong>Have not checked my code</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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