Postgres SQL equivalent of SQL Server for distinct values with all columns

Question

Postgres SQL:

select distinct on(email_addr) * 
from table1 
order by email_addr, created_date desc;

Result - 70 distinct values; with all the row data.

SQL Server equivalent:

select distinct(email_addr), id, first_name, last_name, created_date 
from table1 
order by email_addr, created_date desc;

Result - 160 values; with duplicate values.

I needed help in getting the correct SQL query.

Answer 1

DISTINCT ON is a custom PostgreSQL feature you won't find in other databases.

A more standard way is to use ROW_NUMBER() OVER(PARTITION BY Email_Addr ORDER BY Created_Date) as RN and keep only the rows that have RN=1. You'll have to use a CTE as OVER can't be used in the WHERE clause.

;WITH nondups AS (
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY Email_Addr ORDER BY Created_Date DESC) as RN
    From Table1
    WHERE .....
)
SELECT * 
from nondups
where RN=1

This should run on all databases that support CTEs and ROW_NUMBER(). This includes MySQL 8 and later.

Performance may suffer though, as the ROW_NUMBER needs to be calculated on all the rows of the original result set. This query is probably slow in PostgreSQL too, because the results need to be collected, partitioned and then sorted before the first one can be selected.