using max aggregate function on varchar column vs int column sql server

Posted on : 21-12-2011 | By : Devi Prasad | In : SQL Server Basics, SQLServerPedia Syndication

Share:

0


MAX aggregate Function behaves in a different way on numeric columns when compared to alpha-numeric columns.

consider the below TSQL script:
[sql]
DECLARE @vTable TABLE (ID INT)

INSERT INTO @vTable(ID)
SELECT 1
UNION
SELECT 199
UNION
SELECT 99

SELECT MAX(ID) FROM @vTable
[/sql]

We can expect the result of the MAX(ID) to be 199.

Now consider this TSQL Script:
[sql]
DECLARE @vTable1 TABLE (ID varchar(10))

INSERT INTO @vTable1(ID)
SELECT 1
UNION
SELECT 199
UNION
SELECT 99

SELECT MAX(ID) FROM @vTable1
[/sql]

Now if you expect the result to be 199 again then you are wrong.
The result is 99.

Why this difference:
ID in the second script is a varchar column.
so for varchar being alpha numeric the ascii codes start with NULL for dec(0) till dec(255).
1 comes at around dec(49), 9 comes at dec(57).
So it compares 1 in 199 with 9 in 99 and sorts out that 99 is greater.
Hence the result 99.

Below query you can see the data in sorted format on the ID (varchar) column.
[sql]
DECLARE @vTable1 TABLE (ID varchar(10))

INSERT INTO @vTable1(ID)
SELECT 1
UNION
SELECT 199
UNION
SELECT 99

SELECT ID FROM @vTable1 order by ID asc
[/sql]

Results in sql server management studio:
1
199
99




References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Write a comment



Tags: , , , ,