sql: what does Max() -1 mean?

Question

I have some questions regarding Max()

1. What does the following query mean?

   SELECT MAX(X) -1 FROM T

2. I learned that the syntax should be: SELECT (MAX(X) -1) as max_minus_one FROM T no?

3. Do aggregation function (i.e. Max()) must be followed by GRUOP BY ?

Solution 1

MAX(x) - 1 simply means the max value of x in the table minus one.

You can always use parenthesis and aliases (as some_cool_name) to make thing clearer, or to change names in the result. But the first syntax is perfectly valid.

You only need GROUP BY if you intend to select anything more that the aggregated value, like this for instance:

select
    userName,
    avg(age)
from
    users
group by
    userName

Solution 2

SELECT MAX(X) -1 

and

SELECT (MAX(X) -1)

are the same.

In this case you do not need a GROUP BY since you are not selecting other non aggregated fields.

Solution 3

A. It taxes the maximum value and subtracts 1.

B. If it is only an agregate function, then no. If you specify another column, then you have to use it in GROUP BY.

Solution 4

That would mean "one less than the maximum value in column X". (a) No, that's adding some stuff. (b) No, not when you're selecting a single column like this.

View more solutions