CODEWITHSUNDEEP
sqlselectmax
Bartias
Bartias

Reputation: 461

SQL MAX function in non-numeric columns

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
https://i.sstatic.net/gObvQ.png

Upvotes: 46

Views: 157737

Answers (3)

trogdor
trogdor

Reputation: 1656

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.

Upvotes: 43

Hart CO
Hart CO

Reputation: 34774

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

Upvotes: 2

Artem Tsetkhalin
Artem Tsetkhalin

Reputation: 249

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.

Upvotes: 6

Related Questions