Difference between MAX(NUMERIC) function and MAX(VARCHAR) function in redshift

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.

Fetch the 'nth' element or row from redshift table

Purpose:

A simple query to fetch the nth row in a database table.

        SELECT * 
            FROM tablename 
               LIMIT 1
                 OFFSET n-1;

OFFSET plays a vital role in this query. This query not only works in redshift. All DBs which support postgres queries support OFFSET.

If you need the 21st row data, then the OFFSET will have value as 20. Since OFFSET will omit or skip the first 20 rows and start fetching with the 21st row.

Delete duplicate data from redshift table.

Purpose:

I am tired searching a way to eliminate duplicate data in redshift tables.

Here I am with a simple query to avoid duplicate records in redshift.
 

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER ( PARTITION BY column1,column2,column3,... ORDER BY sortcolumn ASC ) rownum 
FROM tablename)
WHERE rownum = 1;

ORDER BY clause places a major role in this query. Usually, there is a single column which is responsible for the duplicate records. Use that column in the ORDER BY clause.

How this query avoids duplicate?

Follow the below approach to avoid to the duplicate in records or delete duplicate records.

Consider the below Table "Employee" which has two duplicate records

EmpId	EmpName	Salary
1	AAAA	20000
2	BBBB	30000
2	BBBB	30000
3	CCCC	25000
4	DDDD	20000
4	DDDD	20000

 

Now create a temporary table of the same schema and insert data into that temp table using the below query

INSERT into Employee_temp 
SELECT EmpId, EmpName, Salary 
From(
     SELECT *, 
                ROW_NUMBER() OVER ( PARTITION BY EmpId,EmpName,Salary ORDER BY EmpId ASC ) rownum 
     FROM Employee)
WHERE rownum = 1;


Now the temp table will have unique data as below

EmpId	EmpName	Salary
1	AAAA	20000
2	BBBB	30000
3	CCCC	25000
4	DDDD	20000

Now drop the Employee table and Rename the Employee_temp table to Employee.

Hope this should avoid duplicate records or delete duplicate records.