Link to home
Start Free TrialLog in
Avatar of G F
G FFlag for Canada

asked on

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

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:


User generated image


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:



User generated image

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!
Avatar of ste5an
ste5an
Flag of Germany image

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.
Order by cast(substring(XTDNumber,charindex('-',XTDNumber) + 1,len(XTDNumber)) as decimal(4,2)) desc
Avatar of G F

ASKER

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;

Open in new window


This is the following output:

User generated image
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!


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.
Avatar of G F

ASKER

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; 

Open in new window


It looks good (so far):  

User generated imageA 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.



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

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;
@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).
Avatar of G F

ASKER

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:

User generated image
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.



Avatar of G F

ASKER

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.

Open in new window

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

Avatar of G F

ASKER

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:

User generated image

However, it just errors out with a generic SQL syntax error 1064, when I run the query.
Yup, DESC is only needed for the ORDER BY clause, Remove it.
Avatar of G F

ASKER

I removed the three DESC , however still same issue:

User generated image


Yeah, obviously a typo in SELECT..
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)
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of G F

ASKER

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):

User generated image
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.
Avatar of G F

ASKER

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

User generated image
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


User generated image
Avatar of G F

ASKER

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



User generated image

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

Avatar of G F

ASKER

@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;

Open in new window


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:

User generated image
I'll do some more testing and see how it goes.
Avatar of G F

ASKER

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!