how to get MAX Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to get MAX Value

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-07-23 : 01:43:18

if I have a column, Column_Name and it has values

Column_Name
T1
T2
T3
.
.
.
.
T99
.
.
.
.
T864

HOw I can get the MAX column value i.e., T864. When I run the query select MAX(column_name) from A

it returns T99, while MAX value is T864. How can I get the MAX value?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-23 : 02:05:21

SELECT Column_Name
FROM (SELECT TOP 1 Column_Name, LEFT(Column_Name, 1) + RIGHT(REPLICATE('0', 10) + RIGHT(Column_Name, LEN(Column_Name) - 1), 10) as Col2
FROM Table_Name
ORDER By Col2 DESC) as a

Duane.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-23 : 02:06:26

I didn't think MAX worked with non-numeric datatypes.

Try clipping off the T with some of the SQL string functions and then wrap that with the MAX function. I would code it for you here, but I am too tired to even think about it.

Lookup string functions in BOL and check the examples.

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-23 : 02:08:15

quote:
Originally posted by ditch

SELECT Column_Name
FROM (SELECT TOP 1 Column_Name, LEFT(Column_Name, 1) + RIGHT(REPLICATE('0', 10) + RIGHT(Column_Name, LEN(Column_Name) - 1), 10) as Col2
FROM Table_Name
ORDER By Col2 DESC) as a

Looks like Duane did it for you.

-ec

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-07-23 : 03:07:24

Thanks Duane
I did it this way
SELECT TOP 1 MAX(column_name)
FROM table_name
GROUP BY column_name
HAVING LEN(column_name) = (SELECT MAX(LEN(column_name)) from table_name)
ORDER BY column_name DESC

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-23 : 03:27:43

You shouldn't need the group by or having clause.

SELECT TOP 1 column_name
FROM table_name
WHERE len(column_name) = (SELECT max(Len(Column_name)) FROM table_name )
ORDER BY column_name DESC

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources