Database Reference
In-Depth Information
• To compare case-insensitive strings in case-sensitive fashion, order the values using
a case-sensitive collation:
SELECT
MIN
(
str_col
COLLATE
latin1_general_cs
)
AS
min
,
MAX
(
str_col
COLLATE
latin1_general_cs
)
AS
max
FROM
tbl
;
• To compare case-sensitive strings in case-insensitive fashion, order the values using
a case-insensitive collation:
SELECT
MIN
(
str_col
COLLATE
latin1_swedish_ci
)
AS
min
,
MAX
(
str_col
COLLATE
latin1_swedish_ci
)
AS
max
FROM
tbl
;
Another possibility is to compare values that have all been converted to the same
lettercase, which makes lettercase irrelevant. However, that also changes the re‐
trieved values:
SELECT
MIN
(
UPPER
(
str_col
))
AS
min
,
MAX
(
UPPER
(
str_col
))
AS
max
FROM
tbl
;
• Binary strings compare using numeric byte values, so there is no concept of letter‐
case involved. However, because letters in different cases have different byte values,
comparisons of binary strings effectively are case sensitive. (That is,
a
and
A
are
unequal.) To compare binary strings using a case-insensitive ordering, convert
them to nonbinary strings and apply an appropriate collation:
SELECT
MIN
(
CONVERT
(
str_col
USING
latin1
)
COLLATE
latin1_swedish_ci
)
AS
min
,
MAX
(
CONVERT
(
str_col
USING
latin1
)
COLLATE
latin1_swedish_ci
)
AS
max
FROM
tbl
;
If the default collation is case insensitive (as is true for
latin1
), you can omit the
COLLATE
clause.
8.5. Dividing a Summary into Subgroups
Problem
You want a summary for each subgroup of a set of rows, not an overall summary value.
Solution
Use a
GROUP
BY
clause to arrange rows into groups.