SQL SERVER - Understanding how MIN(text) works

Question

I'm doing a little digging and looking for a explanation on how SQL server evaluates MIN(Varchar).

I found this remark in BOL: MIN finds the lowest value in the collating sequence defined in the underlying database

So if I have a table that has one row with the following values:

Data  AA AB AC 

Doing a SELECT MIN(DATA) would return back AA. I just want to understand the why behind this and understand the BOL a little better.

Thanks!

Answer 1

It's determined by the collation (sort order). For most cultures the collation order is the same as the alphabetical order in the English alphabet so:

• 'AA' < 'AB'
• 'AA' < 'AC'
• 'AB' < 'AC'

Therefore 'AA' is the minimum value. For other cultures this may not hold. For example a Danish collation would return 'AB' as the minimum because 'AA' > 'AB'. This is because 'AA' is treated as equivalent to 'Å' which is the last letter in the Danish alphabet.

 SELECT MIN(s COLLATE Danish_Norwegian_CI_AS) FROM table1;  min_s AB 

To get an "ordinary" sort order use the Latin1_General_Bin collation:

 SELECT MIN(s COLLATE Latin1_General_Bin) FROM table1;  min_s AA 

To reproduce this result you can create this test table:

 CREATE TABLE table1 (s varchar(100)); INSERT INTO table1 (s) VALUES ('AA'), ('AB'), ('AC'); 

Answer 2

No, MIN is used in a SELECT statement that scans more than one line. It takes a column as an argument, and returns the "lowest" value (again, according to the collation sequence) found in that column.

Used without a GROUP BY clause, the result set will have a single row, and the value of MIN will be the lowest value found in that column. Used with a GROUP BY clause, the result set will have one row for each group and the value of MIN will be the lowest value in that column for any row in the group.