Column 'Csv.FolderId' is invalid in the select list because it is not contained in either-CodePudding

Table Csv has many columns including FirstName, Lastname, ProjectName, BuildingNumber etc. I want to find all the records have the same FirstName-LastName combinations.

However the below code give me error Column Csv.ProjectName is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT *
FROM Csv
GROUP BY FIrstName, Lastname
HAVING COUNT(*) > 1

How can I tweak the code to fix the error?

CodePudding user response：

The error message is correct. You can specify FirstName, LastName in the select list. All other columns need to be an aggregate. For example, MAX(Address) would be okay if address is a column in the table. (You won't know what record the MAX value came from if there is more then two records. Using MAX or MIN can result in a record with column values coming from different records. So you can't just pick the MAX of every column unless the logic is such that the result does not need to be the values from one specific record.)

One way is to get the "keys" to what you want and then join to get the entire record. (Windowed functions is another means.)

SELECT k.NameCount, c.*
FROM Csv c
INNER JOIN (
    SELECT FirstName, Lastname, COUNT(*) as [NameCount]
    FROM Csv
    GROUP BY FirstName, Lastname
    HAVING COUNT(*) > 1
) k
ON k.FirstName = c.FirstName AND k.Lastname = c.Lastname
ORDER BY k.NameCount DESC