CODEWITHSUNDEEP
sql-servermaxsql-order-by
Manoj Nayak
Manoj Nayak

Reputation: 2509

SQL Server Select MAX Version Value

I have two columns in a table called id and version which looks like below : 

ID | Version |
1  | A.15    |
1  | Z.6     |
1  | C.5     |
1  | BD.3    |
1  | BD.2    |
1  | AB.13   |
2  | C.45    |
2  | Z.56    |
2  | Z.8     |

My requirement is I have to select max(Version) for each id in the table. In this case I should get following result :

|ID | MAX(Version)|
| 1 | BD.3       |
| 2 | Z.56        |

But I am getting following result :

|ID | MAX(Version)|
| 1 | Z.6         |
| 2 | Z.8         |

when I use this query:

SELECT ID,MAX(Version)
FROM table
GROUP BY ID

Please suggest me good solution to get my desired result. Thank you.

Upvotes: 0

Views: 2090

Answers (5)

Jesuraja
Jesuraja

Reputation: 3844

Try this:

DECLARE @MyTable TABLE(ID INT, Version VARCHAR(10))

INSERT INTO @MyTable
VALUES (1,'A.15'), (1,'Z.6'), (1,'C.5'), (1,'BD.3'), (1,'BD.2'),
       (1,'AB.13'), (2,'C.45'), (2,'Z.56'), (2,'Z.8')

--
SELECT
    ID,
    Version
FROM
(
    SELECT
        ID,
        Version,
        ROW_NUMBER() OVER 
        (
            PARTITION BY    ID
            ORDER BY        LEN(LEFT(Version, CHARINDEX('.',Version)-1)) DESC, 
                            LEFT(Version, CHARINDEX('.',Version)-1) DESC, 
                            CAST(STUFF(Version, 1, CHARINDEX('.',Version),'') AS INT) DESC
        ) AS Pos
    FROM 
        @MyTable
) T
WHERE Pos = 1

Upvotes: 1

Edward N
Edward N

Reputation: 997

This is my way for your case ( We don't care about the letters, only focus on number)

DECLARE @MyTable TABLE(ID INT, Version VARCHAR(10))

INSERT INTO @MyTable VALUES(1,'A.15')
                    ,(1,'Z.6')
                    ,(1,'C.5')
                    ,(1,'BD.34')
                    ,(1,'BD.25')
                    ,(1,'AB.13')
                    ,(2,'C.45')
                    ,(2,'Z.56')
                    ,(2,'Z.8')


SELECT t1.ID, t1.Version 
FROM @MyTable AS t1
INNER JOIN (
    SELECT Id, MAX(CONVERT(INT,SUBSTRING(Version,CHARINDEX('.',Version)+1,LEN(Version)))) AS Number
    FROM @MyTable
    GROUP By ID ) AS t2
    ON t1.ID = t2.ID AND CONVERT(INT,SUBSTRING(t1.Version,CHARINDEX('.',t1.Version)+1,LEN(t1.Version))) = t2.Number

Upvotes: 0

Maulik Shah
Maulik Shah

Reputation: 402

We dont need a group by here. It could be done with a relatively simple query. Logic used in the query is similar to the one used in other answer.

SELECT ID,version
FROM tmp a
where CAST(SUBSTRING(version,charindex('.',version)+1,Len(version)-charindex('.',version)) as integer)=
(select MAX(CAST(SUBSTRING(version,charindex('.',version)+1,Len(version)-charindex('.',version)) as integer)) from tmp b where a.id=b.id)

Upvotes: 0

Tanner
Tanner

Reputation: 22743

You can strip out the numeric part of the string by finding the value after the ., like so:

DECLARE @val VARCHAR(5) = 'BD.34'
SELECT  CONVERT(INT, RIGHT(@val, LEN(@val) - CHARINDEX('.', @val))) AS Result

Result
======
34

You can then factor this in to a subquery to join back on to you main table with GROUP BY and MAX like so:

Runnable code:

CREATE TABLE #Versions
    (
      [ID] INT ,
      [Version] VARCHAR(5)
    );

INSERT  INTO #Versions
        ( [ID], [Version] )
VALUES  ( 1, 'A.15' ),
        ( 1, 'Z.6' ),
        ( 1, 'C.5' ),
        ( 1, 'BD.34' ),
        ( 1, 'BD.25' ),
        ( 1, 'AB.13' ),
        ( 2, 'C.45' ),
        ( 2, 'Z.56' ),
        ( 2, 'Z.8' );

SELECT  v.ID ,
        v.Version
FROM    #Versions v
        INNER JOIN ( 
              SELECT ID , 
                     MAX(CONVERT(INT, 
                         RIGHT(Version, 
                           LEN(Version) - CHARINDEX('.', Version)))) AS VersionNo
                     FROM   #Versions
                     GROUP BY ID
                   ) t ON t.ID = v.ID
                          AND '.' + CONVERT(VARCHAR(5), t.VersionNo) = '.'
                          + RIGHT(v.Version,
                                  LEN(v.Version) - CHARINDEX('.', v.Version))

DROP TABLE #Versions

Produces:

ID  Version
1   BD.34
2   Z.56

The join used will join the MAX version number, held in VersionNo back to the main table, I've added '.' on to the join and have assumed that your versions will always be in the format of: characters followed by a period/full stop and the numeric portion.

Upvotes: 1

apomene
apomene

Reputation: 14389

I think you want the following:

SELECT ID,MAX(SUBSTRING(Version,charindex('.',Version),Len(Version)-charindex('.',Version)))
FROM table
GROUP BY ID

Basically the above code gets the substring after '.', thus the numeric part of Version and finds the max over that part

Upvotes: 0

Related Questions