Return all columns only querying one specific column-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

SELECT * FROM DupeAllTable GROUPBY Dbclacct ORDERBY Dbclacct

An asterisk, which is a wildcard selector will help.