We help IT Professionals succeed at work.

What is the best way to reorder a Column generated from a complex SQL query?

104 Views
Last Modified: 2020-09-29
Hi,
This question is based on a previous question I asked awhile back, that was solved.

For reference this is the link to that question: https://www.experts-exchange.com/questions/29174149/Issue-with-SQL-join-with-4-tables-where-one-is-a-parent-Table.html

So that code is working just fine, but I needed to make a few changes:

1) I've had to add a XTD Column, which is similar to the TD Column.
2) I've changed the reference Columns for the Permits

The changes I've done work pretty good, however I'm having an issue sorting one column.

First here is the working code:


Query


Now for the issue.  I'm ordering the list by XTDNumber, descending, but it's messing up the order a bit.  
Here is the output of the above code:



Order

So the issue is XTD 201-10.0, it's supposed to be at the top.

The order wasn't an issue when I was sorting just a TDNumber in my last question, it was purely a Decimal number.

Now it's a VARCHAR/String, and it's not quite doing what I need.

To complicate things the Twenty is the year the document is created, so in January the last number will reset,  to 21-1.  That may not complicate things, just that the first number is not static.

I'm thinking a possible solution might be to keep sorting by TDNumber, rather than XTDNumber, but keep it hidden?

Any help would be a appreciated.

Thank you!
Comment
Watch Question

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
So the issue is XTD 201-10.0, it's supposed to be at the top.
Nope, it's not. The column is text, and lexical order is applied, when sorting by this column.

The correct approach is: If a column like this needs to be sorted in your described way, then you need to normalize it. Currently it is violating 1NF - columns should only contain atomic values -  as you're storing more than one piece of information in that column.

What you can also do: Split that column into parts and sort by these parts in a view.

Use CHARINDEX() to find the positions of the dash and dot and use SUBSTRING() to cutout the parts. Then CAST() the result to INT. The resulting columns can then be ordered numerically.
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Order by cast(substring(XTDNumber,charindex('-',XTDNumber) + 1,len(XTDNumber)) as decimal(4,2)) desc
G FGeneral Manager

Author

Commented:
Hi, so I'm re opening this, as more testing requires some more input.  
I adapted awking00's code to Mysql with the following line:

ORDER BY cast(substring(XTDNumber,LOCATE('-',XTDNumber) + 1,LENGTH(XTDNumber)) as decimal(8,1)) DESC;

This is the following output:


So the issue is, 21-1.0 represents the first document from the new year (2021), but it should be at the top.
Any suggestions would be appreciated!

Thank you!


ste5anSenior Developer
CERTIFIED EXPERT

Commented:
As I already wrote, the sort order is lexical. If you want to sort by parts of your unnormalized value, then you need to parse out each part and specify it as separate column in the ORDER BY.
G FGeneral Manager

Author

Commented:
Hi Ste5an, thank you for the quick reply.
I do understand that, and when I started working on the problem I realized it would not work.

XTD number 20-20.0 would translate into 2020.0 and 21-1.0 would translate to 211.0.  When I order them by decimal or int, either way 211.0 (211) will never come out on top, since 2020.0 (2020) is the larger value.

The document number (succeeding the dash) always resets to '1.0' when the calendar year flips in January.

I was thinking maybe adding a second Order Column, like so:

ORDER BY TDMoveStart DESC, cast(substring(XTDNumber,LOCATE('-',XTDNumber) + 1,LENGTH(XTDNumber)) as DECIMAL(20,1)) DESC; 

It looks good (so far):  

A little hesitant because the decimal represents the revision number of the document.

Document 20-4.1 means it's the Fourth document created in the year 2020 and it's the second copy/revision (.0 being the first).

If I allow the date stamp  'TDMoveStart', which I'm now depending on for the order, to be changed when someone creates a revision, it'll throw the order.  In this case, to keep the order, the revision must keep that date static.

A second potential issue is that TDMoveStart is variable in the original document to begin with.  When the document is created, they can set it so it starts anytime in the future.  If they create a new document before that start of the last, I think the order can get messed up.



ste5anSenior Developer
CERTIFIED EXPERT

Commented:
I was thinking maybe adding a second Order Column [..]
As I said, you need to normalize it or at least parse the atomic values. E.g. something like this:

ORDER BY TDMoveStart DESC,                                                              -- Not sure whether this is needed.
    SUBSTRING(XTDNumber, 1, LOCATE('-', XTDNumber) - 1) DESC,                           -- 2 digit year.
    SUBSTRING(XTDNumber, LOCATE('-', XTDNumber) + 1, LOCATE('.', XTDNumber) - 1) DESC,  -- Document number.
    SUBSTRING(XTDNumber, LOCATE('.', XTDNumber) + 1, 1024) DESC;                        -- Revision number.

Open in new window


EDIT:
awking00 made point about the year, which is fixed. Thus it could be simplified:

ORDER BY TDMoveStart DESC,                                 -- Not sure whether this is needed.
    SUBSTRING(XTDNumber, 1, 2) DESC,                             -- 2 digit year.
    SUBSTRING(XTDNumber, 3, LOCATE('.', XTDNumber) - 1) DESC,    -- Document number.
    SUBSTRING(XTDNumber, LOCATE('.', XTDNumber) + 1, 1024) DESC; -- Revision number.

Open in new window

awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Just add the first part to your order by -
ORDER BY cast(substring(XTDNumber,1,2) as unsigned) DESC, cast(substring(XTDNumber,LOCATE('-',XTDNumber) + 1,LENGTH(XTDNumber)) as decimal(8,1)) DESC;
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
@awking00: The revision number is counter without leading zero / padding.

Document 20 Revision 20 is 20.20 and document 20 revision 9 is 20.9.

Revision 9 < revision 20, but 20.9. > 20.20 (lexical and numeric sort).
G FGeneral Manager

Author

Commented:
Thanks for the information!
I removed the 2nd order dealing with the Date, since it would be preferable to make it work without.

I've tried both examples, and It looks like it's having an issue with the Document Number as it grows.
The output is the same for both.


I plugged them in to my HeidiSQL SQL command generator, and here is the output:


The Year will always be two digit, and the revision number will always be one.

However the document number can/should be allowed to grow.
It seems like it's ordering the document properly, but double digit's being ordered by the first number.



G FGeneral Manager

Author

Commented:
So, playing around some more, it seems like CHAR_LENGTH might be better than LENGTH, just re wrote some of the code, but still the same output as last post above:

ORDER BY   
    SUBSTRING(XTDNumber, 1, 2) DESC,                                -- 2 digit year.
    SUBSTRING(XTDNumber, 4, CHAR_LENGTH(XTDNumber) - 3) DESC,       -- Document number.
    SUBSTRING(XTDNumber, CHAR_LENGTH(XTDNumber), 1) DESC;          -- Revision number.
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
To debug what you're doing, move the ORDER BY parsing to the SELECT..

SELCET *,
    SUBSTRING(XTDNumber, 1, 2) DESC,                          
    SUBSTRING(XTDNumber, 4, CHAR_LENGTH(XTDNumber) - 3) DESC,       
    SUBSTRING(XTDNumber, CHAR_LENGTH(XTDNumber), 1) DESC
FROM yourTable;

Open in new window

G FGeneral Manager

Author

Commented:
Ok, I just started over, and copied the code, I assume you meant get rid of all  the joins and everything else?
Here is what it looks like:



However, it just errors out with a generic SQL syntax error 1064, when I run the query.
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Yup, DESC is only needed for the ORDER BY clause, Remove it.
G FGeneral Manager

Author

Commented:
I removed the three DESC , however still same issue:




ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Yeah, obviously a typo in SELECT..
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
The first part and third parts are easy. First part is simply left(XTDMumber,2) and the third part  is right(XTDMumber,1)
the issue is converting the second part to numeric. This is complicated by the fact that it can be 1 o2 2 digits. To accomplish this use the following:
cast(substring(XTDNumber,charindex('-',XTDNumber) + 1,charindex('.',XTDNumber) - charindex('-',XTDNumber) - 1) as unsigned)
so the order by becomes
order by left(XTDNumber,2),
cast(substring(XTDNumber,charindex('-',XTDNumber) + 1,charindex('.',XTDNumber) - charindex('-',XTDNumber) - 1) as unsigned)
right(XTDNumber,1)
Information Technology Specialist
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
G FGeneral Manager

Author

Commented:
Sorry, yes, lol dunno how I messed that one up...

The query ran, here is the output (basically it's how it's listed by Primary Key (Id) in the Database):

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
btw, I would explicitly test:

SELECT XTDNumber,                                 
    SUBSTRING(XTDNumber, 1, 2) ,                             -
    SUBSTRING(XTDNumber, 3, LOCATE('.', XTDNumber) - 1) ,    
    SUBSTRING(XTDNumber, LOCATE('.', XTDNumber) + 1, 1024)
FROM yourTable;

Open in new window

Post its output.
G FGeneral Manager

Author

Commented:
@ste5an,
Ok, I ran the code as you recommended here is the output:

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Ah, my bad, forgot that the result of LOCATE() is absolut and we need relative values (length):

CREATE TABLE Test ( XtdNumber VARCHAR(255) );

INSERT INTO Test ( XtdNumber )
VALUES ( '20-4.1' );

INSERT INTO Test ( XtdNumber )
VALUES ( '20-11.92' );

SELECT XTDNumber,                                         
    SUBSTRING(XTDNumber, 1, 2) AS YearPart,           
    SUBSTRING(XTDNumber, 4, LOCATE('.', XTDNumber) - 4) AS DocumentNumber,    
    SUBSTRING(XTDNumber, LOCATE('.', XTDNumber) + 1, 1024) AS RevisionNumber
FROM Test;

Open in new window


Capture.PNG
G FGeneral Manager

Author

Commented:
Ok, so just ran it through my table, and here is the output:




Assuming this is now correct, how does this translate back into the ORDER part of the query?  Or is there a different issue?

G FGeneral Manager

Author

Commented:
@awking00
Ok, so I had to adjust your code a little, since MariaDB does not like CHARINDEX.

ORDER BY 
LEFT(XTDNumber,2) DESC,
CAST(SUBSTRING(XTDNumber,Locate('-',XTDNumber) + 1,Locate('.',XTDNumber) - Locate('-',XTDNumber) - 1) as unsigned) DESC,
RIGHT(XTDNumber,1) DESC;

After running the above code, it looks like it ordered everything. As a test, I changed XTDNumber 20-12.0 to 20-120.0, to see how it will order a document with three digits, and here is the output:


I'll do some more testing and see how it goes.
G FGeneral Manager

Author

Commented:
So, looks like everything is working now, I've tried several different combinations of XTD number and it sorts great.
I also moved the year in my Date/Time so the numbers generate with 2021, and it also worked.
Thank you!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.