Introduction:
Here a post with quite interesting topic.
Many might not know about the hidden functionality of MAX() function in redshift query. Not only redshift, all other queries that support postgresql as one of basic query language.
Difference between MAX(NUMERIC) function and MAX(VARCHAR) function:
MAX(NUMERIC):
Many might know MAX(NUMERIC) function.SELECT MAX(salary) as SALARY
FROM employee
WHERE experience > 3;
Lets consider the following table employee
| ID | NAME | DESIGNATION | EXPERIENCE | SALARY |
|---|---|---|---|---|
| 1 | Employee1 | Software Engineer | 2 | 30000 |
| 2 | Employee2 | Software Engineer | 1 | 25000 |
| 3 | Employee3 | Senior Software Engineer | 4 | 50000 |
| 4 | Employee4 | Senior Software Engineer | 5 | 55000 |
| 5 | Employee5 | Software Engineer | 1 | 20000 |
| 6 | Employee6 | Team Lead | 8 | 75000 |
| 7 | Employee7 | Trainee Software Engineer | 0 | 15000 |
| 8 | Employee8 | Software Engineer | 5 | 55000 |
The result of the MAX() query against the table will be
| SALARY |
|---|
| 75000 |
Hence it is clear that the query returned the maximum or largest salary of the employee whose experience is greater than 3.
MAX(VARCHAR):
Let we now dive into the different thing called MAX(VARCHAR) function.
What will happen when we apply max function to a column with varchar type? Lets see
SELECT MAX(designation) as DESIGNATION
FROM employee;
The above query will result
| DESIGNATION |
|---|
| Software Engineer |
Shocking!!!!
MAX(VARCHAR) will return the maximum repeated or frequently used value.
In our example, Software Engineer value is repeated four times.
Keep searching for a better post.
