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!

Holytide

Just FYI, I think the edit to this question (while it makes the data "look" correct) doesn't express the intent of the OP who wants all those values in one row, not three. – Tor

@Larry Lustig: actually, I think the OP's question is misleading; I doubt he has one table with one row and three values in it - he probably wants a table with a single column and three rows, each with one of those values. How else would he do a MIN(Data) on that table?? – Barbecue

It's unclear, but I believe that he does, in fact, believe that MIN operates within a single row (cause he said "one row"). I think he's confusing it with a function that takes several arguments at returns the smallest). – Tor

do you mean that the table has one column with three rows, maybe? If there's truly just one row, selecting MIN(DATA) would return "AA AB AC". – Mullins

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');

Wreck

Of course numbers stored as text also go in alphabetical order not numeric order and unexpected results often happen. For instance 110 would sort in front of 2 because alphabetically anything that begins with 1 is earlier than anything that begins with 2. – Matthia

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.

Tor

+1 - and AA is "less" than AB because it is first alphabetically – Koerlin

Answer 3

min(x), where is a char (string) type -- char(), varchar(), nchar(), nvarchar(), finds the lowest value in the group, based on SQL's string comparison rules:

• if two strings differ in length, the shorter is padded with SP characters (spaces) to the length of the longer.
• comparison proceeds left-to-right, character by character, according to the rule of the collation sequence in use.
• in comparisons, the value NULL compares lower than any non-null values (the ISO/ANSI SQL standard says that it is an implementation choice as to whether NULL collates lower or higher than any non-null value).

So, if you have a table

create table foo
(
  myString varchar(16) not null ,
)

then running the query

select min(myString) from foo

will give you the same result set as if you executed

set rowcount 1

select myString
from foo
order by myString

set rowcount 0

You are basically ordering the set in ascending sequence and selecting the first value. MAX(), or course, gives you the inverse, ordering the set in descending sequence and selecting the first value.

Jaclyn

+1 for the ISO/ANSI SQL standard says that it is an immplementation choice as to whether NULL collates lower or higher than any non-null value – Stephanstephana