StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you want to select from a table (USERMASTER), generate a calculated rank and then sort by that given rank, here's how you can do that effectively.</p> <p>1) <strong>Determine your ranking criteria</strong> - Figure out what matters to your rank, whether it's money spent, student grade, etc, knowing what your metrics are will help.</p> <p>2) <strong>Write your logic</strong> - CASE statements are great when more than one condition is included. For example if you wanted to give a higher rank to a student with both a GPA above 3.0 and a grade in a class equal to an "A", but you want to give a lower rank to lower GPA, you might say:</p> <pre><code>SELECT CASE WHEN student_grade = 'A' AND student_gpa > 3.0 THEN 4 ELSE 3 END rank FROM students ORDER BY rank DESC </code></pre> <p>This will create a rank column based on student_grade and student_gpa logic, then sort the students based on this calculated rank in order of highest rank first.</p> <p>3) <strong>Test your cases</strong> - Verify that your logic gave you the results your looking for by reviewing some sample data. For my example (since I'm unaware of your criteria:</p> <pre><code>student student_grade student_gpa John A 3.5 Mike B 2.9 </code></pre> <p>My results from running the above (with group by on student_name):</p> <pre><code>student_name rank John 4 Mike 3 </code></pre> <p>My results lined up with my expected outcome (John's rank is higher at 4 and also shows up on top of my list due to order by) and we are done!</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