CODEWITHSUNDEEP
sql-server
Kentgi
Kentgi

Reputation: 308

How to get Max value that has been mixed with character in sqlserver

i have a table defined like this:

Name       Value
-----      -----
Alice      MN21
Bob        MN22

i want to get the max value like 

select Name,MAX(Value) as Value from #Character

and it will show

Name      Value
----      -----
Bob       MN22

Any ideas?

Upvotes: 0

Views: 69

Answers (5)

Ramesh Kharbuja
Ramesh Kharbuja

Reputation: 407

simplicity is best

select top 1 Name,Value from #Character order by Value desc

Upvotes: 1

ashkufaraz
ashkufaraz

Reputation: 5297

Try this

select Name,Value from #Character where value in (
   select MAX(CAST(value AS int)) from #Character)

Upvotes: 0

etsa
etsa

Reputation: 5060

Do you need something like this?

CREATE TABLE #CH (NAME VARCHAR(20), VALUE VARCHAR(10))
INSERT INTO #CH VALUES ('Alice', 'MN21'), ('Bob', 'MN22'), ('Angie', 'AO42'),('Karl', 'AO27');
SELECT NAME, MAXVAL, VALUE
FROM #CH
INNER JOIN (SELECT SUBSTRING(VALUE,1,2) AS LEFTPART, MAX(SUBSTRING(VALUE,3,2)) AS MAXVAL
            FROM #CH 
            GROUP BY SUBSTRING(VALUE,1,2)) B ON #CH.VALUE = B.LEFTPART+B.MAXVAL

Output:
NAME                 MAXVAL VALUE
-------------------- ------ ----------
Bob                  22     MN22
Angie                42     AO42

Upvotes: 1

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

You can use SUBSTRING to get only numeric for same format as below:

SELECT TOP(1)
    Name, 
    Value,
    SUBSTRING(Value,3,LEN(Value)) AS MaxValue
FROM @tblTest order by CAST(SUBSTRING(Value,3,LEN(Value)) AS INT) DESC

Upvotes: 1

BhandariS
BhandariS

Reputation: 604

Try this 

select Name,MAX(Value REGEXP '[0-9]{5}') as Value from #Character

Upvotes: 0

Related Questions