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

Answer 1

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.