Using the results of a function multiple times for duplicates - SQL

Question

I am trying to produce a result that shows duplicates in a table. One method I found for getting duplicates and showing them is to run the select statement again through an inner join. However, one of my columns needs to be the result of a function, and the only thing I can think to do is use an alias, however I can't use the alias twice in a SELECT statement.

I am not sure what the best way to run this code for getting the duplicates I need.

My code below

SELECT EXTRACT(YEAR_MONTH FROM date) as 'ndate', a.transponderID  
FROM dispondo_prod_disposition.event a
    inner JOIN (SELECT EXTRACT(YEAR_MONTH FROM date) as ???, 
                        transponderID, COUNT(*)
                FROM dispondo_prod_disposition.event
                GROUP BY mdate, transponderID 
                HAVING count(*) > 1 ) b
ON ndate = ???  
AND a.transponderID  = b.transponderID 
ORDER BY b.transponderID

Sample data and desired results will help your question. – Stu Feb 25 '22 at 17:57 — Stu, Feb 25 '22 at 17:57

score 0 · Answer 1 · answered Feb 25 '22 at 18:18

SELECT b.ndate, transponderID  
FROM       dispondo_prod_disposition.event a
INNER JOIN ( SELECT EXTRACT(YEAR_MONTH FROM date) as ndate, 
                    transponderID
             FROM dispondo_prod_disposition.event
             GROUP BY 1, 2
             HAVING COUNT(*) > 1 ) b USING (transponderID)
WHERE b.ndate = ???  -- for example, WHERE b.ndate = 202201
ORDER BY transponderID

Using the results of a function multiple times for duplicates - SQL

1 Answers1