TSQL, Order by case, and I need to order by version
-
This worked fine until I got to version 11, where the version numbers sorted like this -
vers_id0, 1, 10, 2, 3, 4, 5, 6, 7, 8, 9, I was expecting 0, 1, 2, 3, 4, .... I'm thinking that I need another sort clause but sure how to integrate it into my case But then I could be wrong, and my case statements may just need to be better. Everything I tried failed. Looking for help on this to keep my customers project clean.SELECT
Proj_Stage,
vers_id,
vers_note,
markup_price,
sell_price,
CONVERT(CHAR(19), sell_date, 120)
FROM proj_vers
WHERE proj_id = '$projectNumber'
ORDER BY CASE
WHEN CONVERT(VARCHAR, Proj_Stage) = 'designing' THEN 0
WHEN CONVERT(VARCHAR, Proj_Stage) = 'engineering' THEN 1
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2
WHEN CONVERT(VARCHAR, Proj_Stage) = 'finished' THEN 3
ENDI tried this
END, vers_id
And I tried this
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2, vers_id DESC
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
This worked fine until I got to version 11, where the version numbers sorted like this -
vers_id0, 1, 10, 2, 3, 4, 5, 6, 7, 8, 9, I was expecting 0, 1, 2, 3, 4, .... I'm thinking that I need another sort clause but sure how to integrate it into my case But then I could be wrong, and my case statements may just need to be better. Everything I tried failed. Looking for help on this to keep my customers project clean.SELECT
Proj_Stage,
vers_id,
vers_note,
markup_price,
sell_price,
CONVERT(CHAR(19), sell_date, 120)
FROM proj_vers
WHERE proj_id = '$projectNumber'
ORDER BY CASE
WHEN CONVERT(VARCHAR, Proj_Stage) = 'designing' THEN 0
WHEN CONVERT(VARCHAR, Proj_Stage) = 'engineering' THEN 1
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2
WHEN CONVERT(VARCHAR, Proj_Stage) = 'finished' THEN 3
ENDI tried this
END, vers_id
And I tried this
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2, vers_id DESC
If it ain't broke don't fix it Discover my world at jkirkerx.com
Just a stab in the dark, but you can Cast/Convert vers_id to a int. Cast(vers_id as int) in the order by should do what you want. Assuming vers_id is will always convert to an int. varchars will be sorted in the way you are experiencing.
Jack of all trades, master of none, though often times better than master of one.
-
Just a stab in the dark, but you can Cast/Convert vers_id to a int. Cast(vers_id as int) in the order by should do what you want. Assuming vers_id is will always convert to an int. varchars will be sorted in the way you are experiencing.
Jack of all trades, master of none, though often times better than master of one.
Great idea! I just checked the table and vers_id was set to char. Just crafted this and it does what I want now. I didn't think of that, where it sorted as chars and not numbers. Good eye, or stab in the dark, very helpful and spot on.
SELECT
Proj_Stage,
CAST(vers_id AS int),
vers_note,
markup_price,
sell_price,
CONVERT(CHAR(19), sell_date, 120)
FROM proj_vers
WHERE proj_id = '$projectNumber'
ORDER BY CASE
WHEN CONVERT(VARCHAR, Proj_Stage) = 'designing' THEN 0
WHEN CONVERT(VARCHAR, Proj_Stage) = 'engineering' THEN 1
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2
WHEN CONVERT(VARCHAR, Proj_Stage) = 'finished' THEN 3
END, CAST(vers_id AS int) ASCIf it ain't broke don't fix it Discover my world at jkirkerx.com
-
Just a stab in the dark, but you can Cast/Convert vers_id to a int. Cast(vers_id as int) in the order by should do what you want. Assuming vers_id is will always convert to an int. varchars will be sorted in the way you are experiencing.
Jack of all trades, master of none, though often times better than master of one.
Your Awesome! :)
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
Your Awesome! :)
If it ain't broke don't fix it Discover my world at jkirkerx.com
Your welcome. Been there done that as they say.
Jack of all trades, master of none, though often times better than master of one.
-
Great idea! I just checked the table and vers_id was set to char. Just crafted this and it does what I want now. I didn't think of that, where it sorted as chars and not numbers. Good eye, or stab in the dark, very helpful and spot on.
SELECT
Proj_Stage,
CAST(vers_id AS int),
vers_note,
markup_price,
sell_price,
CONVERT(CHAR(19), sell_date, 120)
FROM proj_vers
WHERE proj_id = '$projectNumber'
ORDER BY CASE
WHEN CONVERT(VARCHAR, Proj_Stage) = 'designing' THEN 0
WHEN CONVERT(VARCHAR, Proj_Stage) = 'engineering' THEN 1
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2
WHEN CONVERT(VARCHAR, Proj_Stage) = 'finished' THEN 3
END, CAST(vers_id AS int) ASCIf it ain't broke don't fix it Discover my world at jkirkerx.com
Off topic - I would assume that "Proj_Stage" would be in another table and should have a sequence no in that table, this would eliminate the case statement in the where clause. If it is input text then you have another problem altogether!
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
Off topic - I would assume that "Proj_Stage" would be in another table and should have a sequence no in that table, this would eliminate the case statement in the where clause. If it is input text then you have another problem altogether!
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
I'm stuck with the existing database to make it backwards capable, with older construction projects. Every record has a ... project number project stage version number This table keeps track of the versions of the construction project. As typical, customers that have construction done always change their mind during the construction process, and don't understand once you pull a permit from the city, you can't change the project. But they change it anyways and then you have to get a new permit. I didn't get any documentation, nor useful comments and had to reverse engineer the old version to make this new version of the software. And it was very broken and needed fixing, thus below ...
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
I'm stuck with the existing database to make it backwards capable, with older construction projects. Every record has a ... project number project stage version number This table keeps track of the versions of the construction project. As typical, customers that have construction done always change their mind during the construction process, and don't understand once you pull a permit from the city, you can't change the project. But they change it anyways and then you have to get a new permit. I didn't get any documentation, nor useful comments and had to reverse engineer the old version to make this new version of the software. And it was very broken and needed fixing, thus below ...
If it ain't broke don't fix it Discover my world at jkirkerx.com
Even if you cannot change the existing tables in the database, you should be able to create new entities. I'd still create another table with 2 columns in it as suggested by @Mycroft Holmes[^] as suggested above[^] (cols:
Proj_StageandSortSequence), indexed byProj_Stage. ThenLEFT JOIN ProjStageSortSequence AS ps ON proj_vers.Proj_Stage = ps.Proj_Stage
and change the
ORDER BYto beORDER BY ps.SortSequence, vers_id -- With conversion for vers_id if still NVARCHAR
. You would also need to change
Proj_Stagein theSELECTtoproj_vers.Proj_Stage. These changes will save loads ofCONVERT()operations and should (not tested) run faster as SQL SERVER is very good at optimizing joins with low nos of rows. This leaves the existing table unaltered and would be more efficient that the query it already has. It would not be as efficient or as normalised as using the sort sequence in the existing table; but it is a stepwise improvement. -
Even if you cannot change the existing tables in the database, you should be able to create new entities. I'd still create another table with 2 columns in it as suggested by @Mycroft Holmes[^] as suggested above[^] (cols:
Proj_StageandSortSequence), indexed byProj_Stage. ThenLEFT JOIN ProjStageSortSequence AS ps ON proj_vers.Proj_Stage = ps.Proj_Stage
and change the
ORDER BYto beORDER BY ps.SortSequence, vers_id -- With conversion for vers_id if still NVARCHAR
. You would also need to change
Proj_Stagein theSELECTtoproj_vers.Proj_Stage. These changes will save loads ofCONVERT()operations and should (not tested) run faster as SQL SERVER is very good at optimizing joins with low nos of rows. This leaves the existing table unaltered and would be more efficient that the query it already has. It would not be as efficient or as normalised as using the sort sequence in the existing table; but it is a stepwise improvement.Took a few minutes for me to absorb, but I get it now, and that's an enhancement that would be backwards compatible. I would be able to apply this new table to every other table that uses the project stages, which is pretty much about 15 more tables. I'll dabble with this tonight at home in my new home office. Thanks!
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
This worked fine until I got to version 11, where the version numbers sorted like this -
vers_id0, 1, 10, 2, 3, 4, 5, 6, 7, 8, 9, I was expecting 0, 1, 2, 3, 4, .... I'm thinking that I need another sort clause but sure how to integrate it into my case But then I could be wrong, and my case statements may just need to be better. Everything I tried failed. Looking for help on this to keep my customers project clean.SELECT
Proj_Stage,
vers_id,
vers_note,
markup_price,
sell_price,
CONVERT(CHAR(19), sell_date, 120)
FROM proj_vers
WHERE proj_id = '$projectNumber'
ORDER BY CASE
WHEN CONVERT(VARCHAR, Proj_Stage) = 'designing' THEN 0
WHEN CONVERT(VARCHAR, Proj_Stage) = 'engineering' THEN 1
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2
WHEN CONVERT(VARCHAR, Proj_Stage) = 'finished' THEN 3
ENDI tried this
END, vers_id
And I tried this
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2, vers_id DESC
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
Just whole integers, maybe up to 20 if the customer gets real bad, or excited about their swimming pool while it's being built.
If it ain't broke don't fix it Discover my world at jkirkerx.com