Return all columns only querying one specific column

August 12, 2022

I have two working queries that return the values I want however they only return the column which I am querying how can I return all columns without querying on all?

SELECT DupeAllTable.Dbclacct from DupeAllTable GROUP BY Dbclacct
Order By Dbclacct


SELECT DupeAllTable.Dbclacct
FROM DupeAllTable
GROUP BY Dbclacct
HAVING COUNT(Dbclacct) > 2

>Solution :

If you want the whole row for all duplicate rows, you can use a window function:

SELECT *
FROM (
    SELECT x.*
         , COUNT(1) OVER (PARTITION BY Dbclacct) AS cnt
    FROM DupeAllTable x
) AS T
WHERE cnt > 1

Note, that you will get the cnt attribute in your result set.

If you have the misfortune of using a DBMS that does not support window functions, you can do a self join:

SELECT y.*
FROM (
    SELECT Dbclacct
    FROM DupeAllTable x
    GROUP BY Dbclacct
    HAVING COUNT(1) > 2
) AS y
JOIN Dbclacct y
    USING (Dbclacct)