Geek Answers Handbook

Split the line and return to mssql

I need to find a way to get the data with the highest version number.

Here is my database design:

VERSIONNUMBER - varchar(15) DOWNLOADPATH - varchar(100)

Suppose I have entries like:

 VERSIONNUMBER -------- DOWNLOADPATH 1.1.2 a.com 1.1.3 b.com 2.1.4 c.com 2.1.5 d.com 2.2.1 e.com

I need to record a record with version number 2.2.1. Need help with sql though :)

Thanks for any help

+7
source share
7 answers

Try the following:

 with a as ( select * from (values ('1.1.2'),('1.1.3'),('2.1.4 '), ('2.1.5'), ('2.2.1') ) as b(c) ) select c, PARSENAME(c,1),PARSENAME(c,2), PARSENAME(c,3) from a order by convert(int,PARSENAME(c,3)), convert(int,PARSENAME(c,2)), convert(int,PARSENAME(c,1))

Inspired by: http://www.sql-server-helper.com/tips/sort-ip-address.aspx

 with a as ( select * from (values ('1.1.2'),('1.1.3'),('2.1.4 '), ('2.1.5'), ('2.2.1') ) as b(c) ), x as ( select c, convert(int,PARSENAME(c,3)) * 100 + convert(int,PARSENAME(c,2)) * 10 + convert(int,PARSENAME(c,1)) * 1 as the_value from a ) select c from x where the_value = (select MAX(the_value) from x)

In software development, it is typical to find a minor version number that has two digits in it, the version number does not have any bearing with a number, so version 1.12 is greater than 1.5; to compensate for this, you must substitute the numbers accordingly:

  -- Use this, the query above is not future-proof :-) with a as ( select * from (values ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c) ), x as ( select c, convert(int,PARSENAME(c,3)) * 100*100*100 + convert(int,PARSENAME(c,2)) * 100*100 + convert(int,PARSENAME(c,1)) * 100 as the_value from a ) select c, the_value from x order by the_value

Output:

 2.1.4 2010400 2.1.5 2010500 2.1.12 2011200 2.2.1 2020100

If you do not take this into account (as with the following request):

 with a as ( select * from (values ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c) ), x as ( select c, convert(int,PARSENAME(c,3)) * 100 + convert(int,PARSENAME(c,2)) * 10 + convert(int,PARSENAME(c,1)) * 1 as the_value from a ) select c, the_value from x order by the_value; -- KorsG answer has a bug too with a as ( select * from (values ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c) ), x as ( select c, CAST(REPLACE(c, '.', '') AS int) as the_value from a ) select c, the_value from x order by the_value

These two queries will lead to the same (wrong) conclusion:

 c the_value 2.1.4 214 2.1.5 215 2.2.1 221 2.1.12 222

The values ​​2.2.1 and 2.1.12 overlap. This also happens when you simply delete points and directly convert the resulting string to int. 2.1.12 become two thousand one hundred twelve, 2.2.1 become two hundred twenty one. 2.2.1 more than 2.1.12, not less

+4
source 
 select top 1 DOWNLOADPATH from YourTable order by cast(parsename(VERSIONNUMBER, 3) as int) desc, cast(parsename(VERSIONNUMBER, 2) as int) desc, cast(parsename(VERSIONNUMBER, 1) as int) desc
+2
source

Alternatively, instead of multiplying each group of numbers, you can use ranking:

 with a as ( select * from (values ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c) ), x as ( select c, Ranking = RANK() over(order by convert(int,PARSENAME(c,3)), convert(int,PARSENAME(c,2)), convert(int,PARSENAME(c,1))) from a ) select * from x order by ranking

Productivity:

 c Ranking 2.1.4 1 2.1.5 2 2.1.12 3 2.2.1 4

Final request:

 with a as ( select * from (values ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c) ), x as ( select c, Ranking = RANK() over(order by convert(int,PARSENAME(c,3)), convert(int,PARSENAME(c,2)), convert(int,PARSENAME(c,1))) from a ) select * from x where Ranking = (select MAX(ranking) from x)

Output:

 c Ranking 2.2.1 4

Another simple approach, sort in descending order, just enter the first line:

 with a as ( select * from (values ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c) ), x as ( select c, Ranking = RANK() over(order by convert(int,PARSENAME(c,3)) desc, convert(int,PARSENAME(c,2)) desc, convert(int,PARSENAME(c,1)) desc) from a ) select * from x where Ranking = 1
+1
source

If you are on SQL Server 2008, you can use the HIERARCHYID data HIERARCHYID .

 SELECT VersionNumber, DownloadPath FROM (VALUES ('1.1.2','a.com'), ('1.1.3','b.com'), ('2.1.4','c.com'), ('2.1.5','d.com'), ('2.2.1','e.com') ) AS T(VersionNumber, DownloadPath) ORDER BY CAST('/' + VersionNumber + '/' AS HIERARCHYID) DESC
+1
source

This will work, but it is not very - I would definitely think about changing the way we store version numbers.

 concat( right(concat(repeat("0",5), substring_index(VERSIONNUMBER,".",1)),5), right(concat(repeat("0",5), substring_index(substring_index(VERSIONNUMBER,".",2),".",-1)),5), right(concat(repeat("0",5), substring_index(VERSIONNUMBER,".",-1)),5))

It basically turns "1.24.937" into "000010002400937" , which is then correctly sorted as a string.

0
source

Personally, I like the @Mikael version, but obviously this is not quite as portable for other RDBMSes ...

How about this? Quick and dirty work and works for three-digit versions (as indicated in your example.)

The hack here is to understand that "2.59", for example, is a valid number, not a real integer, so you only need to break the line in one place - you need the main version number, and rest. This is disgusting, but when it came out of sight, I thought I would share it, because it is at least short and disgusting.

 SELECT TOP 1 downloadpath FROM version_table ORDER BY CAST(LEFT(VERSIONNUMBER, CHARINDEX( '.', VERSIONNUMBER) - 1) AS INTEGER) DESC, CAST(SUBSTRING(VERSIONNUMBER, CHARINDEX( '.', VERSIONNUMBER) + 1, 100) AS FLOAT) DESC

Of course, the real answer is to redesign the database to either split version numbers, or include a build number, or something like that ...

0
source 
 SELECT downloadpath FROM TABLE WHERE versionnumber = (SELECT MAX(VersionNumber) FROM TABLE)

There may be a more beautiful way to do this.

-2
source

More articles:


All Articles