Two years ago, I published an article about migrating an Oracle SQL query using the "KEEP (DENSE_RANK LAST ...)" clause to PostgreSQL. In the article I demonstrated several queries that I transformed.
Recently, just out of curiosity, I wondered how chatGPT would migrate the query. So I asked the AI how it would migrate the query. And the AI gave me a solution which I had not considered in the article.
Here is the question I asked, followed by the AI's answer.
My question:
AI, I have the following query in Oracle 12c. How would you change the query in PostgreSQL?
SELECT EMPLOYEE_ID
, MAX(SALE_ID) KEEP (DENSE_RANK LAST ORDER BY SALE_DATE) AS MAX_SALE_ID
, MAX(SALE_DATE) KEEP (DENSE_RANK LAST ORDER BY SALE_DATE) AS SALE_DATE
, MAX(EUR_VALUE) KEEP (DENSE_RANK LAST ORDER BY SALE_DATE) AS MAX_VALUE
, MAX(PRODUCT_ID) KEEP (DENSE_RANK LAST ORDER BY SALE_DATE) AS PRODUCT_ID
FROM SALES
GROUP BY EMPLOYEE_ID
ORDER BY EMPLOYEE_ID;
AI's answer:
In PostgreSQL, you can achieve the equivalent result of the Oracle query using the DISTINCT ON clause along with a subquery. Here's how you can rewrite the query:
SELECT DISTINCT ON (EMPLOYEE_ID)
EMPLOYEE_ID,
SALE_ID AS MAX_SALE_ID,
SALE_DATE,
EUR_VALUE AS MAX_VALUE,
PRODUCT_ID
FROM SALES
ORDER BY EMPLOYEE_ID, SALE_DATE DESC;
Explanation:
We use DISTINCT ON (EMPLOYEE_ID) to ensure that we only get one row per unique EMPLOYEE_ID.
The ORDER BY clause is used to sort the rows within each EMPLOYEE_ID group based on SALE_DATE in descending order (DESC). This way, the most recent sale will appear first within each group.
Since we are sorting the rows in descending order, the first row for each EMPLOYEE_ID will contain the most recent sale, and we can directly retrieve the desired columns for that row.
This PostgreSQL query will give you the same results as the original Oracle query, returning the most recent sale details for each EMPLOYEE_ID.
--This is the end of the AI's answer.
I was impressed by the AI's answer. In the near future, sql tuners like me may find it challenging to get a job. I hope this change happens after I retire.
Checking the performance of the migrated query is left as an exercise for the readers.
