CODEWITHSUNDEEP
sql-server
Roofus McDuffle
Roofus McDuffle

Reputation: 105

How does SQL Server treat MAX of a varchar column?

If I do something like 

select max(case when col1 = 5 then 'banana' else '0')

how will SQL Server choose? What is the maximum value for the column?

Upvotes: 0

Views: 80

Answers (2)

JanR
JanR

Reputation: 6130

I believe that when you do a MAX(string) in SQL Server it orders the values alphabetically in an ascending order and picks the last value (bottom of the list)

Can you post the rest of your query? Or explain a bit more one what you are trying to achieve?

Upvotes: 1

mellamokb
mellamokb

Reputation: 56779

This is equivalent to taking data that looks like this:

col5
1
3
5
7

Then converting it to this

col5 | case when...
1    | 0
3    | 0
5    | banana
7    | 0

Then doing max on the above results. The result falls into four cases:

  1. If the data does not contain any rows, then you will get NULL.
  2. If data only has rows where col1 = 5 is true, then you will get banana.
  3. If data only has rows where col1 = 5 is false, then you will get 0.
  4. If there are both rows where col1 = 5 is true and false, you will get banana, because banana > 0 according to lexicographical comparison

I have generally only seen this type of query when trying to do the equivalent of PIVOT without using the PIVOT statement.

Upvotes: 3

Related Questions