Why does this not sort like I'm expecting?

Forum Moderators: open

Message Too Old, No Replies

Why does this not sort like I'm expecting?

csdude55

7:11 am on Mar 7, 2021 (gmt 0)

I'm importing a client's data from a CSV file in to MariaDB, using PMA. I'm selecting 2 columns, like so:

SELECT colA, colB FROM table ORDER BY colA ASC, colB DESC

The results are:

colA  | colB
1. All | 1. PSS
1. All | 1. Partner
1. All | 1. PSS
1. All | 1. Kane
1. All | 1. PSS
1. All | 1. PSS
1. All | 1. PSS
1. All | 1. Kane
1. All | 1. PSS
1. All | 1. Partner
1. All | 1. Partner

The "1. " is part of the value, but I'm leaving it here just in case you see an issue.

I'm expecting the second column to be Kane, Partner, then PSS, but it's not. I've looked through the pre-imported CSV in plain text and don't see any extra spaces or control characters, so I'm at a loss on why it's not sorting the way I'm expecting.

Do you guys and gals see anything I'm overlooking, or have any suggestions on what else I might look for?

lammert

1:32 am on Mar 14, 2021 (gmt 0)

Did you try the following?

SELECT colA, colB FROM table ORDER BY CAST(colA as CHAR(50)) ASC, CAST(colB as CHAR(50)) DESC

csdude55

6:21 am on Mar 14, 2021 (gmt 0)

I had not, but I just now did... still no go.

I also used REGEX_REPLACE() to remove any opening characters that weren't letters, just to make sure there weren't any invisible characters gumming up the works, but still no go.

SELECT
 REGEXP_REPLACE(colA, '^[^a-z]+', '') AS colA_modified,
 REGEXP_REPLACE(coB, '^[^a-z]+', '') AS colB_modified
FROM table ORDER BY CAST(colA_modified AS CHAR(50)) ASC, CAST(colB_modified AS CHAR(50)) DESC

It's really not important anymore, I had to move forward with writing a PHP script to do it instead. So at this point it's just for educational purposes, because there's no visible reason why it's sorting like that.