MySQL SELECT order by values of 2 columns

Question

I have a table like this:

CREATE TABLE rows(
    UniqueID VARCHAR(225),
    Previous VARCHAR(225),
    Next VARCHAR(225)
);

With content, that looks like this:

+----------+-----------+-----------+
| UniqueID | Previous  | Next      |
+----------+-----------+-----------+
|    676   | undefined |       219 |
|    890   |       219 | undefined |
|    219   |       676 |       890 |
+----------+-----------+-----------+

As you can see, the rows have UID's, which the Previous and Next columns refer to.

What I now want, is to write a SELECT * statement, that would order all the results, by the Previous and Next fields. The undefined values mark the end elements. How could I achieve that? In the case of the table showed above, the order I'd want is what's shown there, with the last 2 row positions swapped, so Next of row X Points to a UID of row Y, that has a Previous that points to the UID of the row X. etc.

check http://stackoverflow.com/q/12726549/4457531 & http://stackoverflow.com/q/30076307/4457531 — Nolwennig, Aug 30 '16 at 11:21
juergend explained a bit more in the OP. jens, that does value size based sorting so 1; 2; 3 etc, which is not the case here. tim-biegeleisen the one with no Previous is the first element, and the one with no Next is the last. — Rando Hinn, Aug 30 '16 at 11:22
Note that by convention `NULL` is used to signify undefined characteristics - such as orphans. — Strawberry, Aug 30 '16 at 11:38
@Strawberry Yup I know that, that undefined is a sideffect from the fact that the source for the data in the table is JS. — Rando Hinn, Aug 30 '16 at 11:39
This is a special case of a dependency graph. These can be sorted by topological sort, but the algorithms are awkward in SQL. — cliffordheath, Aug 30 '16 at 11:57
So essentially you want to convert an edge list (or adjacency list) into a materialized path? — Strawberry, Aug 30 '16 at 12:23
@Strawberry - `NULL` can be used for other purposes. In this case it means "there is no prev/next", which is more of a "out of band", rather than "undefined", possibly "any" (eg 0/0), or "not yet known" (eg, end_time). — Rick James, Sep 01 '16 at 20:34
Wouldn't this work? `select n1.* from rows n1 left join rows n2 on n1.Next = n2.UniqueID order by n1.Previous ASC;` — Sherif elKhatib, Sep 02 '16 at 17:53

score 2 · Accepted Answer · answered Sep 03 '16 at 03:24

2

What you're trying to create is a recursive query. Unfortunately, MySQL does not make this easy. There are relatively simple solutions if the parents always have an index greater than the children, but that is not the case here. There are several questions discussing this type of problem. The following question has answers that explore the different ways to attempt this type of query including using stored procedures.

How to do the Recursive SELECT query in MySQL?

Going with the stored procedure idea, you could try something like:

CREATE PROCEDURE getInOrder()
  BEGIN
    DECLARE child_id VARCHAR(256);
    DECLARE prev_id VARCHAR(256);
    SELECT UniqueID INTO prev_id FROM rows WHERE Previous = 'undefined';
    SELECT `Next` INTO child_id
    FROM rows WHERE UniqueID = prev_id;
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS (SELECT * FROM rows WHERE 1=0);
    TRUNCATE TABLE temp_table;
    WHILE child_id <> 'undefined' DO
      INSERT INTO temp_table SELECT * FROM rows WHERE UniqueID = prev_id;
      SET prev_id = child_id;
      SELECT `Next` INTO child_id
      FROM rows WHERE UniqueID = prev_id;
    END WHILE;
    INSERT INTO temp_table SELECT * FROM rows WHERE UniqueID = prev_id;
    SELECT * FROM temp_table;
  END;

You can then call the stored procedure to retrieve the table in order.

Working example: http://sqlfiddle.com/#!9/085dec/2

answered Sep 03 '16 at 03:24

Surberus

226
1
6

Hey @Surberus, I am currently using your method, however I started noticing that, with just one row in the table, the entire procedure just hangs. is there a workaround available that would just return that row in this case? – Rando Hinn Oct 26 '16 at 20:19
Hi @RandoHinn. The stored procedure will hang if the table does not have conforming data. The following fiddle shows that the procedure works with one row for as long as that row has `Previous` and `Next` set to 'undefined'. http://sqlfiddle.com/#!9/40317/1 The while loop requires that a row is eventually supplied where `Next` is undefined, otherwise, it will loop forever. You could put in a safety counter to make sure you never loop more than the count of rows in the table. This would also protect against a set of data that loops in on itself. – Surberus Oct 27 '16 at 19:15
Hey, @Surberus Now setting Previous and Next as 'undefined'. Still hangs.. When the table is empty, it works, when it has 1 row, it hangs.. :/ – Rando Hinn Oct 27 '16 at 19:36
It's hard to say what's happening without seeing your code. Also, the requirement for the text 'undefined' was based off of your original question. You can always replace everything with NULL (in the database and the stored procedure) if that makes things easier. If you want to post a fiddle, it would make it easier to troubleshoot. – Surberus Oct 27 '16 at 19:44
I started to make a fiddle.. only to discover sqlFiddle uses MySQL 5.6, and it works there, wheras my environment uses 5.7... I am downgrading MySQL to see if that makes a difference. Will report back soon... – Rando Hinn Oct 27 '16 at 19:48
Nope, that's not it... @Surberus, if you have GH, I can let you in on the repo that has this problem – Rando Hinn Oct 27 '16 at 20:04
It turns out I did create an account a while back and never used it. Had to find the password. Anyway, the GitHub account is also Surberus. – Surberus Oct 27 '16 at 20:20
access granted will brief on issue #3 – Rando Hinn Oct 28 '16 at 06:41

score 0 · Answer 2 · answered Sep 01 '16 at 20:30

ORDER BY IFNULL(prev, ''),         -- some value lower than the rest
         IFNULL(next, 'zzzzz')     -- some value higher than all values

(Technically, the first part could be simply prev, without the IFNULL.)

If the ids are really numbers, you should use a numeric datatype such as INT UNSIGNED. If they are really strings, do you need 225?

This assumes that prev < next -- Is that necessarily the case? It seems like arbitrary links might not maintain that. If you need to look at next to load the next row based on UniqueId, the code is much more complex.

Cristian Gonçalves · Answer 3 · 2016-09-02T00:21:08.343

I think this request lacks on details.

But, you want the final result to be like this?

+----------+-----------+-----------+
| UniqueID | Previous  | Next      |
+----------+-----------+-----------+
|    676   | undefined |       219 |
|    219   |       676 |       890 |
|    890   |       219 | undefined |
+----------+-----------+-----------+

If I'm right, you can achieve it with (I named the table as demo):

SELECT d.* FROM (
    SELECT UniqueID, IF(Previous IS NULL, -1, Previous) AS Previous, IF(Next   IS NULL, 999999999999, Next) as Next
    FROM demo
)t
JOIN demo d ON d.UniqueID = t.UniqueID
ORDER BY t.Next, t.Previous
;

So, when Previous is NULL you put it with -1 to ensure he's is the first on the list and when Next is NULL you put it with a very high value to ensure it will be the last on the list... then you just have to order the query by Previous and Next.

I must stress that this solution is focused on presented data.

MySQL SELECT order by values of 2 columns

3 Answers3