SQL Server compare numbers after decimal point

Question

We have some numbers values in SQL server database such like 7.11 and 7.6. Actually 7.11 is later version than 7.6, when using "max(vernum)" in SQL server, it will return 7.6. How can we let SQL server return 7.11? Thanks

Does this answer your question? [How to compare software versions using SQL Server?](https://stackoverflow.com/questions/11364242/how-to-compare-software-versions-using-sql-server) — Rion Williams, Dec 13 '19 at 05:21

score 2 · Accepted Answer · answered Dec 13 '19 at 06:27

You should go read the official docs .

By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale

Try This

SELECT MAX(Version) OVER(ORDER BY CAST(PARSENAME(version, 2) AS INT) DESC,CAST(PARSENAME(version, 1) AS INT) DESC) Version, Version    
FROM yourTable

FIDDLE DEMO

score 1 · Answer 2 · answered Dec 13 '19 at 05:30

Off the top of my head, you could use a TOP query here, using ordering by casting the major and minor version numbers to integers:

SELECT TOP 1 version
FROM yourTable
ORDER BY
    CAST(LEFT(version, CHARINDEX('.', version) - 1) AS INT) DESC,
    CAST(SUBSTRING(version, CHARINDEX('.', version) + 1, LEN(version)) AS INT) DESC;

Demo

score 1 · Answer 3 · answered Dec 13 '19 at 05:33

1

I'll give you a more shorter answer of this.

with cte as (
    select  7.11 as ver
    union all
    select 7.6
)select top 1 ver from cte
      order by parsename(ver, 2), parsename(cast(ver as float), 1)

answered Dec 13 '19 at 05:33

Ed Bangga

12,879
4
16
30

score 1 · Answer 4 · answered Dec 13 '19 at 05:51

You can try this.

CREATE TABLE #Numbers (value float);
INSERT INTO #Numbers (value)
VALUES (7.1);
INSERT INTO #Numbers (value)
VALUES (7.6);
INSERT INTO #Numbers (value)
VALUES (7.11);
INSERT INTO #Numbers (value)
VALUES (1.1);
INSERT INTO #Numbers (value)
VALUES (6.5);

SELECT Top 1 VALUE , Cast(PARSENAME(VALUE,1) as Int) AS DECIMAL_ONLY 
FROM #NUMBERS
order by 2 desc

SQL Server compare numbers after decimal point

4 Answers4

Demo