G F
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:
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:
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!
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:
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:
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!
Order by cast(substring(XTDNumber,charindex('-',XTDNumber) + 1,len(XTDNumber)) as decimal(4,2)) desc
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:
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!
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!
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.
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:
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.
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.
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.
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.
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;
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).
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).
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:
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.
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.
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.
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;
ASKER
Yup, DESC is only needed for the ORDER BY clause, Remove it.
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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;
Post its output.
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;
ASKER
ASKER
@awking00
Ok, so I had to adjust your code a little, since MariaDB does not like CHARINDEX.
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.
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.
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!
I also moved the year in my Date/Time so the numbers generate with 2021, and it also worked.
Thank you!
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.