Complex MySQL sort

Question

I've done an almost full re-write on this question as I'm still stuck... Looking for a way to view the data in a results table without having to use php or some other data interpreter/programming to see the results how I want to see them.

Basic results table is like this (jumble it up more, if you want):

 id1 |  id2 | name   | ord
 ------------------------------
 a1  | null | name1  |  0
 a2  | null | name2  |  1
 a3  | null | name3  |  2
 b1  |  a1  | name4  |  0
 b2  |  b1  | name5  |  0
 b3  |  a1  | name6  |  1
 b4  |  a1  | name7  |  2
 c1  |  a2  | name8  |  0
 c2  |  a2  | name9  |  1
 d1  |  a2  | name10 |  2
 d2  |  a2  | name11 |  3
 d3  |  a2  | name12 |  4
 d4  |  a3  | name13 |  0
 c3  |  d4  | name14 |  0
 c4  |  c3  | name15 |  1
 d5  |  b2  | name16 |  0

The result I'm looking for is this:

 id1 |  id2 | name   | ord
 -----------------------------
 a1  | null | name1  |  0  <--group header, signified by `id2` is null
 b1  |  a1  | name4  |  0  <--item that's parent to line below
 b2  |  b1  | name5  |  0  <--item that's parent to line below
 d5  |  b2  | name16 |  0  <--last child item
 b3  |  a1  | name6  |  1  <--special case where `ord` takes over
 b4  |  a1  | name7  |  2  <--`id2` is still the same, so `ord` sort
 a2  | null | name2  |  1  <--next group header
 c1  |  a2  | name8  |  0  <--`id2` is a2, so name2 is parent, `ord` sort
 c2  |  a2  | name9  |  1  <--same
 d1  |  a2  | name10 |  2  <--same
 d2  |  a2  | name11 |  3  <--same
 d3  |  a2  | name12 |  4  <--same
 a3  | null | name3  |  2  <--next group header
 d4  |  a3  | name13 |  0  <--`id2` is a3, so name3 is parent
 c3  |  d4  | name14 |  0  <--`id2` is d3, so name13 is parent
 c4  |  c3  | name15 |  1  <--`id2` is c3, so name14 is parent

Wordy explanation of what I need

Essentially, I need id2 to be "group headers" that are sorted by ord. And then below each of them, I need the rows sorted in such a way that id2 is equal to id1 of the line above it and anytime id2 is the same as id2 for the line above it, it should sort by ord.

When it runs out of rows where id2 matches the last id1, the next group header should come up and sorting should start over again with the next id2 matching id1 of the new group header.

Any item where id2 is null is a group header, but in those groups, the ord starts over based on the "parent" line. Some of those lines are also parents to other items, thus the complex nature of the sort

There are other columns in the actual results table, so I simplified this down to the columns needed to actually do the sorting.

Table data explained

The actual id columns are char(36), name is varchar(1024), ord is int(11), and the other columns in the table run the gamut... I should also mention that these results are from a JOIN that's done for the purposes of limiting the results of the primary table where this data is found.

id1 is the primary key and unique, doesn't really pertain to the sort order, except that the all rows with id2 equal to a given id1 group header should appear below it. id2 is a reference to a "parent line id" and thus why it's null for group headers; they're the parent-most lines and have their own sort order via ord. name is actually item names expect for the group header rows which are categories names.

As an example, say the group headers are food groups and the other entries in are types of food. So one group header would be "fruit," the next "vegetables," etc. It's actually A/V gear, but fruit is probably easier to understand.

What I've Done

My attempts at this have been all over the map. I've tried various order by, field(), if(), and I've considered subqueries, but my skills just aren't up to hashing this out on my own. I think if I can get pointed in the right direction, I can handle combining what I have to get the right result. Unfortunately, out of frustration, I've deleted any semi-working code, but it was probably nothing more then an ORDER BY with a non-working CASE statement.

Key Points:

When id2 is null, it's a group header and group headers should be sorted lowest to highest (0, 1, 2) by their ord field.
Any row that has id1 equal to another row's id2 is its parent. In other words, if a row is found where id2 equals id1 of another row, it should be placed beneath it.
If multiple rows exist where id2 equals an id1, the sort should first consider #2, then sort by ord the lowest to highest.

randy · Accepted Answer

So, it's been a while. Honestly, I've forgotten what this was meant to represent, but I believe I was trying to sort line items as they appear in an application. Assuming that was the case, I got an answer from one of our developers. We ended up using a combination of COALESCE() and LPAD().

Say these were line items on a list, but some are nested and they all live in the same table. The only relation is knowing the parent line's id and an ordinal. We came up with this:

SELECT CONCAT_WS('-', LPAD(COALESCE(item5.ordinal),4,'0'),`
   LPAD(COALESCE(item4.ordinal),4,'0'),
   LPAD(COALESCE(item3.ordinal),4,'0'),
   LPAD(COALESCE(item2.ordinal),4,'0'),
   LPAD(COALESCE(item.ordinal),4,'0')) AS line_order
FROM main_lines as main
LEFT JOIN sub_line as item on item.id = main.id
LEFT JOIN sub_line as item2 on item2.id = item.parent_line_id
LEFT JOIN sub_line as item3 on item3.id = item2.parent_line_id
LEFT JOIN sub_line as item4 on item4.id = item3.parent_line_id
LEFT JOIN sub_line as item5 on item5.id = item4.parent_line_id

ORDER BY line_order;

The COALESCE allowed us to null-safe the rows and the LPAD allowed the CONCAT_WS to provide a proper sort order and honor the 0's in each result.

Edit: Thanks to Paul Maxwell for the inspiration to prove this in a SQL Fiddle.

Complex MySQL sort

Answers (2)

Related Questions