StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to assign virtual auto-increment rank with limit of 4?
text
Body
copied!<p>Simply put, I would like rid to order my pid. </p> <p>Instead of creating an extra table called 'rank' with 4 rows I would like to be able to just attach a virtual rank that gives the same effect. My rank is just there to give a position to my tags. Since I have 4 tags per id I would have a value of 1-4 enumerating those tags. </p> <p>This way I won't have to manually put in the same numbers over and over again.</p> <p>My tables: </p> <pre> people tags_rel tags rank id | etc id | pid | tid | rid id | tag id -----+----- ---+------+-----+---- ---+-------- -- 2345 | 1 | 2345 | 2 | 1 1 | bread 1 2346 | 2 | 2345 | 3 | 2 2 | water 2 2347 | 3 | 2345 | 1 | 3 3 | bear 3 4 | 2345 | 6 | 4 4 | milk 4 ---+------+-----+---- 5 | hotdogs 5 | 2346 | 3 | 1 6 | 2346 | 4 | 2 7 | 2346 | 2 | 3 8 | 2346 | 5 | 4 ---+------+-----+---- 9 | 2347 | 6 | 1 10 | 2347 | 1 | 2 11 | 2347 | 4 | 3 12 | 2347 | 5 | 4 ---+------+-----+---- </pre> <p>My query:</p> <pre><code>SELECT p.id as pid, t.tag as tname, tr.tid as trid, r.id as rank FROM people AS p RIGHT JOIN tags_rel AS tr ON tr.pid = p.id LEFT JOIN tags AS t ON tr.tid = t.id LEFT JOIN rank AS r ON tr.rid = r.id </code></pre> <p>MySQL expected results:</p> <pre> > +--------+------------+--------+------+ > | pid | tname | trid | rank | > +--------+------------+--------+------+ > | 2345 | water | 2 | 1 | > | 2345 | bread | 1 | 2 | > | 2345 | cereal | 3 | 3 | > | 2345 | milk | 4 | 4 | > | 2346 | cereal | 3 | 1 | > | 2346 | milk | 4 | 2 | > | 2346 | water | 2 | 3 | > | 2346 | hotdogs | 5 | 4 | > | 2347 | chocolate | 6 | 1 | > | 2347 | bread | 1 | 2 | > | 2347 | bread | 4 | 3 | > | 2347 | bread | 5 | 4 | > +--------+------------+--------+------+ </pre> <p>I am using rank as position. So whatever tags are attached to pid I would like to show by first position. Since the tags are different for each pid they would have different positions. I may want to call tname=water by rank=1 and it would display all the 'water' tags in position 1 only.</p> <p>Thanks!</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