SQL MAX function in non-numeric columns

Question

As far as I understand the MAX function, it shall return a maximum value from a given column. In case of numeric values, for example a salary column, it is clear for me - and this is the only application I find in tutorials. However, I have a problem to understand how does it work in case of non-numeric columns.

My problems originates from this exercise (on sql-ex.ru)

Find out makers who produce only the models of the same type, and the number of those models exceeds 1. The table "Product" includes information about the maker, model number, and type ('PC', 'Laptop', or 'Printer'). One of the solutions to this is:

SELECT maker,
       MAX(type) AS type
FROM   product
GROUP  BY maker
HAVING COUNT(DISTINCT type) = 1
       AND COUNT(model) > 1

I don't understand the function of max - what does it count? I tried a simpler query to understand it, but it only made it more difficult.

SELECT maker,
       MAX(type) AS type, COUNT(type) AS QTY
FROM product
GROUP BY maker
ORDER BY maker

The returned set was

maker  type      QTY
A      Printer   7
B      PC        2
C      Laptop    1
D      Printer   2
E      Printer   4

The MAX(type) seems to me to show a random value e.g. why for the maker B the result is PC and not Laptop? Why for E it is Printer and not PC?

Full Table

score 39 · Answer 1 · edited Oct 24 '16 at 12:01

39

The functions MAX, MIN, etc. use the lexicographic order when applied to text columns. Therefore, your MAX(type) will return 'Printer' instead of 'PC' because 'Printer' is after (greater than) 'PC' in alphabetic order.

Notice that in your first query the condition HAVING COUNT(distinct type) = 1 means that there can only be a single typevalue for each group. The MAX(type) clause in the select is used because simply type can not be used in the select as it is not in the GROUP BY clause.

edited Oct 24 '16 at 12:01

Prabhat

810
9
33

answered Sep 01 '13 at 13:01

trogdor

1,626
14
17

I see - and if the type was in the GROUP BY clause, it would produce a different result, returning all possible combinations of maker and type? – Bartias Sep 02 '13 at 10:24
1

@Bartias exactly, it would create a different group for each different combination maker-type, and the aggregation functions in your SELECT clause would be applied to these smaller groups – trogdor Sep 02 '13 at 10:35

score 6 · Answer 2 · answered Sep 01 '13 at 13:10

6

In character columns MAX finds the highest value in the collating sequence. In PC and Laptop case: "P" symbol goes after "L" symbol so MAX result is PC. Printer and PC: first letters are equal but "r" symbol goes after "C" so MAX result is Printer.

answered Sep 01 '13 at 13:10

Artem Tsetkhalin

239
2
9

score 2 · Answer 3 · answered Sep 01 '13 at 14:36

MAX() used on a string evaluates the values in alphabetic order and length, a > b, but ab > a.

In your case the HAVING clause is limiting to where the type value is the same for all records for a given maker, so MAX() and GROUP BY are just used to return a single row, and it doesn't matter which value of type it returns because they're the same for all rows that can be returned.

If you change your second query it might help you see how this all works out:

SELECT maker
     , MAX (type) AS maxType
     , MIN (type) AS minType
     , COUNT(DISTINCT type) AS QTY
     , COUNT(model) AS Models
FROM product
GROUP BY maker
ORDER BY maker

Demo: SQL Fiddle

The first query could also be re-written as:

SELECT maker
     , MIN(type)
FROM product
GROUP BY maker
HAVING MAX(type) = MIN(type)
   AND COUNT(model) > 1
ORDER BY maker

SQL MAX function in non-numeric columns

3 Answers3

Linked