sorting mysql table by complex value

Question

My table consists of records where every record has its own history which looks like this:

65465406540-245|65465408540-654

There is no limitation to history entries count (column type is text, so ...). And history entries are ALWAYS sorted from the oldest to the newest. Meaning the newest entry is on the right.

History entries are separated by pipe char "|". One history entry consists of timestamp, dash "-" as a separator and 1-4 digit number which represents user ID who made the change.

Now my problem is I'd like to sort records in my table by the most recent (or the oldest) history entry. How would I do that?

I was thinking about MySQL functions, since result has to come to my PHP script already sorted, but I don't know where to start, so every help is much appreciated.

Jim · Accepted Answer · 2012-08-07 16:12:56Z

1

For oldest you can use RedFilters (now redacted) answer of

ORDER BY LEFT(column_name,11)

Ordering by newest is a bit more fun.

ORDER BY REVERSE(SUBSTR(REVERSE(column_name),1,LOCATE("|",REVERSE(column_name))-1))

Let's break this down. We reverse the column and find the index of the first |

456-04580456456|542-04560456456

We then get the substring up to this point which gives us:

456-04580456456

This is the last item reversed so now we can simply REVERSE the string and order by that to order by the last item:

65465408540-654

This is pretty hideous however. I would consider doing this in PHP if at all possible.

edited Aug 7, 2012 at 16:12

answered Aug 7, 2012 at 15:59

Jim

22.7k6 gold badges55 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

meridius Over a year ago

I may have describe it wrong. What I need is sorting just by the last timestamp (asc/desc). And for that solved I'm truly grateful for @Jim complex query to which I just add ASC, or DESC. Thank you!

meridius · Accepted Answer · 2012-08-10 13:22:30Z

0

I tweaked Jim's query, so it can also work with only one, and/or none history entry and is displaying only timestamp for easier date/time calculations. Here it is:

SELECT *, 
    IF(
        LOCATE('|', history) > 0, 
        LEFT(
            REVERSE(
                LEFT(
                    REVERSE(history), 
                    LOCATE('|', REVERSE(history))-1
                )
            ), 
            10
        ), 
        LEFT(history, 10)
    ) AS last 
FROM table
ORDER BY last DESC

answered Aug 10, 2012 at 13:22

meridius

1,5872 gold badges18 silver badges29 bronze badges

Collectives™ on Stack Overflow

sorting mysql table by complex value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related