CODEWITHSUNDEEP
sqlselectsql-server-2008-r2max
LeeRoy
LeeRoy

Reputation: 33

Get Max for field in SQL according to the datatype

I am trying to get the MAX of a field in SQL. The field takes numeric and non numeric characters. If the field has the values A, B, C the result should be C. But when the field has for example A, B, C, 1, 2 the result should be 2. Can someone please assist me on how I can achieve this??

Thank You

See Image

Upvotes: 2

Views: 75

Answers (4)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this : May solve your problem at some level

DECLARE @T TABLE(data VARCHAR(2))
INSERT INTO @T VALUES('A'),('B'),('C'),('1'),('2')

SELECT TOP(1) data
FROM @T
ORDER BY CASE WHEN ISNUMERIC(data)=1 
              THEN 'Z'+data
              ELSE data 
         END desc

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Do conditional ordering

select n from
(
select '1' as n union all
select '2' as n union all
select 'A' as n union all
select 'B' as n union all
select 'C' as n 
) t
order by case when isnumeric(n)=1 then 1 else 2 end,n desc



select n from
(
select 'A' as n union all
select 'B' as n union all
select 'C' as n 
) t
order by case when isnumeric(n)=1 then 1 else 2 end,n desc

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

This works

SELECT TOP 1 IDValue
FROM
(
SELECT 'A' as IDValue, ISNUMERIC('A') tag
UNION
SELECT 'C', ISNUMERIC('C')
UNION
SELECT 'B', ISNUMERIC('B')
UNION
SELECT '1',ISNUMERIC('1')
UNION
SELECT '2',ISNUMERIC('2')
)tmp
ORDER BY tag DESC,IDValue DESC

Output

2

ISNUMERIC() returns 1 if expression is number else 0 . You can use it in ORDER BY to fetch max value.

Upvotes: 1

Prima Shella
Prima Shella

Reputation: 93

here's the syntax for finding max value with SQL

SELECT MAX(column_name) FROM table_name;

you could refer to http://www.w3schools.com/sql/sql_func_max.asp for further reading and code testing

Upvotes: 0

Related Questions