StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The easiest way would be to put your ordering in a separate table (called <code>ordering_table</code> in this example):</p> <pre><code> id | position ----+---------- 1 | 11 2 | 42 3 | 23 etc. </code></pre> <p>The above would mean "put an <code>id</code> of 1 at position 11, 2 at position 42, 3 at position 23, ...". Then you can join that ordering table in:</p> <pre><code>SELECT t.id, t.col1, t.col2 FROM some_table t JOIN ordering_table o ON (t.id = o.id) ORDER BY o.position </code></pre> <p>Where <code>ordering_table</code> is the table (as above) that defines your strange ordering. This approach simply represents your ordering function as a table (any function with a finite domain is, essentially, just a table after all).</p> <p>This "ordering table" approach should work fine as long as the ordering table is complete.</p> <p>If you only need this strange ordering in one place then you could merge the <code>position</code> column into your main table and add <code>NOT NULL</code> and <code>UNIQUE</code> constraints on that column to make sure you cover everything and have a consistent ordering.</p> <p>Further commenting indicates that you want different orderings for different users and categories and that the ordering will change on a daily basis. You could make separate tables for each condition (which would lead to a combinatorial explosion) or, as Mikael Eriksson and ypercube suggest, add a couple more columns to the ordering table to hold the user and category:</p> <pre><code>CREATE TABLE ordering_table ( thing_id INT NOT NULL, position INT NOT NULL, user_id INT NOT NULL, category_id INT NOT NULL ); </code></pre> <p>The <code>thing_id</code>, <code>user_id</code>, and <code>category_id</code> would be foreign keys to their respective tables and you'd probably want to index all the columns in <code>ordering_table</code> but a couple minutes of looking at the query plans would be worthwhile to see if the indexes get used would be worthwhile. You could also make all four columns the primary key to avoid duplicates. Then, the lookup query would be something like this:</p> <pre><code>SELECT t.id, t.col1, t.col2 FROM some_table t LEFT JOIN ordering_table o ON (t.id = o.thing_id AND o.user_id = $user AND o.category_id = $cat) ORDER BY COALESCE(o.position, 99999) </code></pre> <p>Where <code>$user</code> and <code>$cat</code> are the user and category IDs (respectively). Note the change to a LEFT JOIN and the addition of COALESCE to allow for missing rows in <code>ordering_table</code>, these changes will push anything that doesn't have a specified position in the order to the bottom of the list rather than removing them from the results completely.</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