|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 1,573,
Visits: 11,895
|
|
Ok I have the following data (exhumed out of a production system).
CREATE TABLE #TEst
(
TESTID INT PRIMARY KEY,
VERSIONVAL CHAR(10) NOT NULL
)
INSERT INTO #TEST(TESTID,VERSIONVAL)
SELECT 1,'1.0.0'
UNION ALL
SELECT 575,'7.4.0'
UNION ALL
SELECT 576,'4.4.0'
UNION ALL
SELECT 577,'3.4.0'
UNION ALL
SELECT 578,'7.4.0'
UNION ALL
SELECT 6,'3.0.0'
UNION ALL
SELECT 7,'1.2.2'
UNION ALL
SELECT 579,'6.5.3'
UNION ALL
SELECT 580,'3.5.0'
UNION ALL
SELECT 581,'4.5.0'
UNION ALL
SELECT 11,'2.0.0'
UNION ALL
SELECT 12,'2.0D'
UNION ALL
SELECT 13,'2.0F'
UNION ALL
SELECT 14,'2.0H'
UNION ALL
SELECT 582,'7.5.0'
UNION ALL
SELECT 583,'6.5.3'
UNION ALL
SELECT 17,'2.2.0'
UNION ALL
SELECT 584,'6.5.4'
UNION ALL
SELECT 585,'7.4'
UNION ALL
SELECT 586,'7.5'
UNION ALL
SELECT 21,'2.0E'
UNION ALL
SELECT 589,'7.5.0'
UNION ALL
SELECT 590,'3.5.0'
UNION ALL
SELECT 591,'6.6.0'
UNION ALL
SELECT 592,'7.5.1'
UNION ALL
SELECT 593,'7.5.1'
UNION ALL
SELECT 594,'7.5.0'
UNION ALL
SELECT 595,'7.5.0'
UNION ALL
SELECT 596,'7.5.0'
UNION ALL
SELECT 597,'7.5.0'
UNION ALL
SELECT 598,'7.5.0'
UNION ALL
SELECT 599,'3.6.0'
UNION ALL
SELECT 601,'7.6'
UNION ALL
SELECT 34,'4.0G'
UNION ALL
SELECT 602,'7.6.0'
UNION ALL
SELECT 603,'7.6.0'
UNION ALL
SELECT 37,'5.0'
UNION ALL
SELECT 38,'5.01'
UNION ALL
SELECT 39,'5.02'
UNION ALL
SELECT 40,'5.03'
UNION ALL
SELECT 41,'5.0A'
UNION ALL
SELECT 42,'5.0B'
UNION ALL
SELECT 43,'5.0C'
UNION ALL
SELECT 44,'5.0D'
UNION ALL
SELECT 45,'5.0E'
UNION ALL
SELECT 46,'6.0'
UNION ALL
SELECT 47,'6.01'
UNION ALL
SELECT 48,'6.02'
UNION ALL
SELECT 49,'6.03'
UNION ALL
SELECT 604,'7.6.0'
UNION ALL
SELECT 51,'6.10'
UNION ALL
SELECT 52,'6.10'
UNION ALL
SELECT 608,'4.0.0'
UNION ALL
SELECT 609,'4.0.0'
UNION ALL
SELECT 55,'6.20'
UNION ALL
SELECT 610,'8.0.0'
UNION ALL
SELECT 611,'7.0.0'
UNION ALL
SELECT 98,'10'
UNION ALL
SELECT 59,'1.3.0'
UNION ALL
SELECT 60,'1.3.1'
UNION ALL
SELECT 61,'1.3.2'
UNION ALL
SELECT 612,'3.6.0'
UNION ALL
SELECT 614,'4.6'
UNION ALL
SELECT 71,'1.3.3'
UNION ALL
SELECT 73,'6.30'
UNION ALL
SELECT 74,'7.0'
UNION ALL
SELECT 75,'2.2.0'
UNION ALL
SELECT 79,'1.3.4'
UNION ALL
SELECT 81,'2.0.1'
UNION ALL
SELECT 91,'7.0'
UNION ALL
SELECT 118,'6.20'
UNION ALL
SELECT 168,'3.0'
UNION ALL
SELECT 296,'1'
UNION ALL
SELECT 310,'@2.85'
UNION ALL
SELECT 314,'2.3.2'
UNION ALL
SELECT 318,'15'
UNION ALL
SELECT 319,'2.1.3'
UNION ALL
SELECT 320,'2.3.0'
UNION ALL
SELECT 321,'2.1.3'
UNION ALL
SELECT 76,'2.0.2'
UNION ALL
SELECT 77,'2.1.0'
UNION ALL
SELECT 78,'2.3.0'
UNION ALL
SELECT 80,'1.1.0'
UNION ALL
SELECT 82,'2.2.2'
UNION ALL
SELECT 93,'6.30'
UNION ALL
SELECT 95,'6.03'
UNION ALL
SELECT 97,'6.10'
UNION ALL
SELECT 83,'2.2.3'
UNION ALL
SELECT 84,'2.2.4'
UNION ALL
SELECT 85,'2.2.5'
UNION ALL
SELECT 86,'2.2.6'
UNION ALL
SELECT 87,'2.2.7'
UNION ALL
SELECT 88,'2.2.8'
UNION ALL
SELECT 89,'2.2.9'
UNION ALL
SELECT 90,'2.3.1'
UNION ALL
SELECT 92,'6.30'
UNION ALL
SELECT 94,'6.30'
UNION ALL
SELECT 96,'6.20'
UNION ALL
SELECT 99,'11'
UNION ALL
SELECT 120,'6.032'
UNION ALL
SELECT 122,'3.12-25'
UNION ALL
SELECT 100,'12'
UNION ALL
SELECT 101,'13'
UNION ALL
SELECT 102,'14'
UNION ALL
SELECT 103,'15'
UNION ALL
SELECT 104,'16'
UNION ALL
SELECT 105,'17'
UNION ALL
SELECT 106,'18'
UNION ALL
SELECT 107,'19'
UNION ALL
SELECT 108,'20'
UNION ALL
SELECT 109,'21'
UNION ALL
SELECT 110,'22'
UNION ALL
SELECT 111,'23'
UNION ALL
SELECT 112,'24'
UNION ALL
SELECT 113,'25'
UNION ALL
SELECT 114,'26'
UNION ALL
SELECT 115,'27'
UNION ALL
SELECT 116,'28'
UNION ALL
SELECT 119,'6.101'
UNION ALL
SELECT 121,'6.03'
UNION ALL
SELECT 123,'4.3.02'
UNION ALL
SELECT 124,'4.5.02'
UNION ALL
SELECT 125,'4.5.03'
UNION ALL
SELECT 126,'4.5.04'
UNION ALL
SELECT 127,'4.5.05'
UNION ALL
SELECT 128,'4.6.03C'
UNION ALL
SELECT 129,'4.7.00'
UNION ALL
SELECT 130,'4.7.01'
UNION ALL
SELECT 131,'4.8.00'
UNION ALL
SELECT 132,'6.03'
UNION ALL
SELECT 133,'6.1B'
UNION ALL
SELECT 134,'6.2'
UNION ALL
SELECT 135,'5.0D'
UNION ALL
SELECT 136,'5.0E'
UNION ALL
SELECT 137,'6.03'
UNION ALL
SELECT 138,'6.031'
UNION ALL
SELECT 139,'6.0312'
UNION ALL
SELECT 140,'6.032'
UNION ALL
SELECT 141,'6.10'
UNION ALL
SELECT 162,'6.032'
UNION ALL
SELECT 143,'5.02'
UNION ALL
SELECT 144,'5.0A'
UNION ALL
SELECT 145,'5.0B'
UNION ALL
SELECT 146,'5.0C'
UNION ALL
SELECT 147,'5.0E'
UNION ALL
SELECT 148,'6.03'
UNION ALL
SELECT 149,'6.031'
UNION ALL
SELECT 150,'3.30'
UNION ALL
SELECT 151,'4.00'
UNION ALL
SELECT 152,'6.032'
UNION ALL
SELECT 153,'6.20'
UNION ALL
SELECT 154,'6.30'
UNION ALL
SELECT 155,'7.00'
UNION ALL
SELECT 156,'6.032'
UNION ALL
SELECT 157,'6.20'
UNION ALL
SELECT 158,'6.30'
UNION ALL
SELECT 159,'7.00'
UNION ALL
SELECT 160,'6.031'
UNION ALL
SELECT 161,'2.2.0'
UNION ALL
SELECT 163,'6.302'
UNION ALL
SELECT 164,'6.101'
UNION ALL
SELECT 165,'5.0B1'
UNION ALL
SELECT 166,'6.031'
UNION ALL
SELECT 167,'6.21'
UNION ALL
SELECT 169,'3.0.1.10'
UNION ALL
SELECT 170,'3.5.0.32'
UNION ALL
SELECT 171,'3.5.0.33'
UNION ALL
SELECT 172,'3.5.0.61'
UNION ALL
SELECT 173,'3.5.0.68'
UNION ALL
SELECT 174,'3.5.0.71'
UNION ALL
SELECT 175,'4.0'
UNION ALL
SELECT 176,'4.120'
UNION ALL
SELECT 177,'4.122'
UNION ALL
SELECT 178,'4.124'
UNION ALL
SELECT 179,'4.125'
UNION ALL
SELECT 180,'1.21'
UNION ALL
SELECT 181,'1.31'
UNION ALL
SELECT 182,'2.0'
UNION ALL
SELECT 183,'5.03'
UNION ALL
SELECT 184,'5.0B'
UNION ALL
SELECT 185,'5.0D'
UNION ALL
SELECT 186,'5.0C'
UNION ALL
SELECT 187,'5.0D1'
UNION ALL
SELECT 188,'6.02'
UNION ALL
SELECT 189,'6.03'
UNION ALL
SELECT 190,'6.20'
UNION ALL
SELECT 191,'6.101'
UNION ALL
SELECT 192,'6.10'
UNION ALL
SELECT 193,'5.0E'
UNION ALL
SELECT 194,'5.0A'
UNION ALL
SELECT 195,'6.30'
UNION ALL
SELECT 196,'1.31'
UNION ALL
SELECT 197,'1.4'
UNION ALL
SELECT 198,'1.42'
UNION ALL
SELECT 199,'1.43'
UNION ALL
SELECT 200,'2.0A'
UNION ALL
SELECT 201,'2.0F'
UNION ALL
SELECT 202,'2.0H'
UNION ALL
SELECT 203,'2.5F'
UNION ALL
SELECT 204,'4.0E'
UNION ALL
SELECT 205,'5.0A'
UNION ALL
SELECT 206,'5.0B'
UNION ALL
SELECT 207,'5.0C'
UNION ALL
SELECT 208,'5.0D'
UNION ALL
SELECT 209,'5.0D1'
UNION ALL
SELECT 210,'5.0E'
UNION ALL
SELECT 211,'6.0'
UNION ALL
SELECT 212,'6.02'
UNION ALL
SELECT 213,'6.03'
UNION ALL
SELECT 214,'6.10'
UNION ALL
SELECT 215,'6.20'
UNION ALL
SELECT 216,'6.30'
UNION ALL
SELECT 217,'7.0'
UNION ALL
SELECT 218,'5.0'
UNION ALL
SELECT 219,'5.0B'
UNION ALL
SELECT 220,'5.0D'
UNION ALL
SELECT 221,'5.0E'
UNION ALL
SELECT 222,'6.02'
UNION ALL
SELECT 223,'6.03'
UNION ALL
SELECT 224,'6.10'
UNION ALL
SELECT 225,'6.20'
UNION ALL
SELECT 226,'6.30'
UNION ALL
SELECT 227,'7.0'
UNION ALL
SELECT 228,'1.0.0'
UNION ALL
SELECT 229,'2.0.0'
UNION ALL
SELECT 230,'2.1.0'
UNION ALL
SELECT 231,'2.1.1'
UNION ALL
SELECT 232,'2.1.2'
UNION ALL
SELECT 233,'1.0.0'
UNION ALL
SELECT 234,'2.1.0'
UNION ALL
SELECT 238,'1.22'
UNION ALL
SELECT 239,'1.33'
UNION ALL
SELECT 240,'2.0'
UNION ALL
SELECT 241,'2.01'
UNION ALL
SELECT 242,'2.02'
UNION ALL
SELECT 243,'2.10'
UNION ALL
SELECT 244,'2.20'
UNION ALL
SELECT 245,'6.10'
UNION ALL
SELECT 246,'6.20'
UNION ALL
SELECT 247,'6.30'
UNION ALL
SELECT 248,'1.0G'
UNION ALL
SELECT 249,'3.0D'
UNION ALL
SELECT 250,'3.0I'
UNION ALL
SELECT 251,'4.0'
UNION ALL
SELECT 252,'4.0I'
UNION ALL
SELECT 253,'4.1'
UNION ALL
SELECT 254,'5.0'
UNION ALL
SELECT 255,'5.0A'
UNION ALL
SELECT 256,'5.0B'
UNION ALL
SELECT 257,'5.0C'
UNION ALL
SELECT 258,'5.0D'
UNION ALL
SELECT 259,'5.0E'
UNION ALL
SELECT 260,'5.01'
UNION ALL
SELECT 261,'5.02'
UNION ALL
SELECT 262,'5.03'
UNION ALL
SELECT 263,'6.0'
UNION ALL
SELECT 264,'6.02'
UNION ALL
SELECT 265,'6.03'
UNION ALL
SELECT 266,'7.0'
UNION ALL
SELECT 267,'3.0I'
UNION ALL
SELECT 268,'1.31'
UNION ALL
SELECT 269,'1.32'
UNION ALL
SELECT 270,'1.4'
UNION ALL
SELECT 271,'1.41'
UNION ALL
SELECT 272,'1.42'
UNION ALL
SELECT 273,'1.43'
UNION ALL
SELECT 274,'1.5'
UNION ALL
SELECT 275,'1.6'
UNION ALL
SELECT 276,'5.0D1'
UNION ALL
SELECT 277,'4.0'
UNION ALL
SELECT 278,'4.0D'
UNION ALL
SELECT 279,'4.0E'
UNION ALL
SELECT 280,'5.0E1'
UNION ALL
SELECT 281,'3.1'
UNION ALL
SELECT 282,'2.5'
UNION ALL
SELECT 283,'2.0G'
UNION ALL
SELECT 284,'2.7A'
UNION ALL
SELECT 285,'3.0'
UNION ALL
SELECT 286,'3.01'
UNION ALL
SELECT 287,'3.0C'
UNION ALL
SELECT 288,'3.0D'
UNION ALL
SELECT 289,'3.0E'
UNION ALL
SELECT 290,'3.0F'
UNION ALL
SELECT 291,'3.0G'
UNION ALL
SELECT 292,'@@2.0F'
UNION ALL
SELECT 293,'@3.1.1A'
UNION ALL
SELECT 294,'10.1.@'
I need to sort it
Descending by the first number before the ., and then by the second number after the first . in a numeric sorting order. When I have a non-numeric value I need to ignore it and process the numeric portion before the .
i.e. the results should look like this
VERSIONVAL
------------------
10.1.@
9.3
9.2
9.1
...
@3.1.1A
3.0
@@2.0F
etc...
Looking for something that works first and foremost. Preferably both on SQL 2000 and SQL 2k5+
Best I've come up with so far is below... and it fails on the @@ value
select
*
from #TEST
ORDER BY
CAST(
CASE
WHEN CHARINDEX('.',versionval) = 0 AND ISNUMERIC(versionval) =1 THEN versionval
WHEN CHARINDEX('.',versionval) = 0 AND ISNUMERIC(versionval) = 0 THEN RIGHT(versionval,LEN(versionval)-CHARINDEX('@',versionval))
WHEN ISNUMERIC(left(versionval,charindex('.',versionval)-1))=1 THEN left(versionval,charindex('.',versionval)-1)
ELSE RIGHT(left(versionval,charindex('.',versionval)-1),LEN(left(versionval,charindex('.',versionval)-1))-1) END AS NUMERIC(10,5)) DESC,
pubdata..agn_versions.versionval desc
--Mark Tassin
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
For tips on how to post your problems
http://www.sqlservercentral.com/articles/Best+Practices/61537/
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 170,
Visits: 236
|
|
not fully understanding
are u saying 10.1.@ would come before 17?
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 4:19 PM
Points: 1,035,
Visits: 6,570
|
|
Not sure I follow, but what if you got rid of the @'s for sorting:
select
*, REPLACE(REPLACE(versionval, '@', ''), '@@', ''), CASE CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', ''))
WHEN 0 THEN REPLACE(REPLACE(versionval, '@', ''), '@@', '')
ELSE LEFT(REPLACE(REPLACE(versionval, '@', ''), '@@', ''), CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', '')) - 1) END
from #TEST
ORDER BY CAST(CASE CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', ''))
WHEN 0 THEN REPLACE(REPLACE(versionval, '@', ''), '@@', '')
ELSE LEFT(REPLACE(REPLACE(versionval, '@', ''), '@@', ''), CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', '')) - 1) END AS INT) DESC
, REPLACE(REPLACE(versionval, '@', ''), '@@', '') DESC
bc
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 1,573,
Visits: 11,895
|
|
john scott miller (11/18/2009)
not fully understanding
are u saying 10.1.@ would come before 17?
No... it's not a character comparison... as I said, my query attempt is only semi-working at this point.
Whatever number comes before the first decimal point is the sorted numerically descending, with the number(s) after the remaining deimal point(s) also sorted descending.
So 17 comes first
17
10.4.1
10.3
10.2.9
10.2.1
10.1
@3.1
%2.5
etc.
--Mark Tassin
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
For tips on how to post your problems
http://www.sqlservercentral.com/articles/Best+Practices/61537/
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 1,573,
Visits: 11,895
|
|
bc_ (11/18/2009)
Not sure I follow, but what if you got rid of the @'s for sorting: select
*, REPLACE(REPLACE(versionval, '@', ''), '@@', ''), CASE CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', ''))
WHEN 0 THEN REPLACE(REPLACE(versionval, '@', ''), '@@', '')
ELSE LEFT(REPLACE(REPLACE(versionval, '@', ''), '@@', ''), CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', '')) - 1) END
from #TEST
ORDER BY CAST(CASE CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', ''))
WHEN 0 THEN REPLACE(REPLACE(versionval, '@', ''), '@@', '')
ELSE LEFT(REPLACE(REPLACE(versionval, '@', ''), '@@', ''), CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', '')) - 1) END AS INT) DESC
, REPLACE(REPLACE(versionval, '@', ''), '@@', '') DESC
That might work... I suppose if I removed everything else that's in the ASCII table but numbers and periods... hmmm
--Mark Tassin
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
For tips on how to post your problems
http://www.sqlservercentral.com/articles/Best+Practices/61537/
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Tuesday, March 09, 2010 6:18 AM
Points: 660,
Visits: 514
|
|
Hi Mark,
So it works in 2000 I've had to do away with CTEs so this looks a little convoluted. This looks to work on your test set?
It's a starting point, sort of building the string slicing as I went, so I dare say there will be a way of taking this and building in some clever pattern matching etc.
select * from #test
order by
cast(substring(replace(coalesce(left(versionval,nullif(charindex('.',versionval),0)),versionval),'.',''),patindex('%[0-9]%',replace(coalesce(left(versionval,nullif(charindex('.',versionval),0)),versionval),'.','')),50) as int) desc,
cast(left(right(rtrim(versionval),len(versionval) - nullif(charindex('.',versionval),0)),coalesce(nullif(patindex('%[^0-9]%',right(rtrim(versionval),len(versionval) - charindex('.',versionval))),0)-1,len(right(rtrim(versionval),len(versionval) - charindex('.',versionval))))) as int) desc
--------
I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Yesterday @ 5:07 PM
Points: 3,927,
Visits: 10,634
|
|
i thought maybe using the PARSENAME function might get you to where you wanted...tis is still sorting as text, we could change that a bit, but i thought this was pretty close to what you wanted?
select * ,
PARSENAME(VERSIONVAL,4),
PARSENAME(VERSIONVAL,3),
PARSENAME(VERSIONVAL,2),
PARSENAME(VERSIONVAL,1)
from #TEST
order by
--to use parsename, we need to assume(or force) three periods exist:
CASE
WHEN LEN(VERSIONVAL) = LEN(REPLACE(VERSIONVAL,'.','')) --no periods at all
THEN PARSENAME(VERSIONVAL,1)
WHEN LEN(VERSIONVAL) = LEN(REPLACE(VERSIONVAL,'.','')) + 1 --one period
THEN PARSENAME(VERSIONVAL,2)
WHEN LEN(VERSIONVAL) = LEN(REPLACE(VERSIONVAL,'.','')) + 2 --two periods
THEN PARSENAME(VERSIONVAL,3)
WHEN LEN(VERSIONVAL) = LEN(REPLACE(VERSIONVAL,'.','')) + 3 --three periods
THEN PARSENAME(VERSIONVAL,4)
END
DESC
Lowell
help us help you! if your post doesnt have CREATE TABLE YOURTABLENAME and INSERT INTO YOURTABLE SQL statements, chances are you didn't post enough information for us to help you! If you can, avoid pseudocode and show us your actual query...it makes it much better for you to see a good example with your tablenames you'd understand, and be able to copy and paste for immediate testing.
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 1,573,
Visits: 11,895
|
|
Hey Rob,
That actually works awesomely for 858/860 of my records in production.
I didn't realize how bad this data was until I found two records it doesn't work on. (I tested it against the rest of the data by excluding the records in question with a where clause).
So let's add those as well.
INSERT INTO #TEST(TESTID,VERSIONVAL)
SELECT 498,'CNXN'
UNION ALL
SELECT 670,'8,1.0'
So I checked with the developer working on this, and got the following rule for CNXN Values without numeric values should be sorted alphabetically at the end.
and commas should be treated as decimal points/periods.
--Mark Tassin
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
For tips on how to post your problems
http://www.sqlservercentral.com/articles/Best+Practices/61537/
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 10:22 AM
Points: 1,250,
Visits: 3,257
|
|
How about adding a
CASE WHEN PATINDEX('%[0-9]%',VERISIONVAL) = 0 THEN 1 ELSE 0 END,
In fron
t of the other ORDER BY criteria (Order by anything that has a number first, then the things that don't follow alphabeticaly)
And then use a REPLACE(VERSIONVAL,',','.') to sub out the period for your comma inside that order by to handle those two special situations.
Seth Phelabaum
Consistency is only a virtue if you're not a screwup. 
Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Tuesday, March 09, 2010 6:18 AM
Points: 660,
Visits: 514
|
|
You're right, and looking at it closer the code is actually putting 6.10 as a more recent version than 6.3 (10 > 3 in the way the logic is done)
This code here should sort out the alpha only (and give a final sort to versions with letter like 5.1E and 5.1B), and the version minor number issue.
I've altered the charindex search for the point to a patindex search for the first comma or point, does this look right on your data?
select * from #test
order by
cast(substring(replace(replace(coalesce(left(versionval,nullif(patindex('%[,.]%',versionval),0)),versionval),'.',''),',',''),patindex('%[0-9]%',replace(replace(coalesce(left(versionval,nullif(patindex('%[,.]%',versionval),0)),versionval),'.',''),',','')),50) as int) desc,
cast('.'+left(right(rtrim(versionval),len(versionval) - nullif(patindex('%[,.]%',versionval),0)),coalesce(nullif(patindex('%[^0-9]%',right(rtrim(versionval),len(versionval) - patindex('%[,.]%',versionval))),0)-1,len(right(rtrim(versionval),len(versionval) - patindex('%[,.]%',versionval))))) as money)*1000 desc,
versionval desc
--------
I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams
|
|
|
|
