I know you can remove a column to hide it but what about an aggregate or function?
SELECT MIN([date])AS FIRSTDAY,lastName,firstName,birthdate,gender
FROM [Day] d
GROUP BY lastName,firstName,birthdate,gender
Use CTE. It much easier to add more logic, like ORDER BY
or WHERE
WITH MinDate AS
(
SELECT MIN([date])AS FIRSTDAY, lastName,firstName,birthdate,gender
FROM [Day]
GROUP BY lastName,firstName,birthdate,gender
)
SELECT lastName,firstName,birthdate,gender
FROM MinDate
-- WHERE
-- ORDER BY
If you just want to remove duplicate by lastName,firstName,birthdate,gender, use sub query when date is not duplicated value. By this, you can separate duplication removal logic from presentation.
SELECT D1.lastName, D1.firstName, D1.birthdate, D1.gender, -- More fields
FROM [Day] D1
WHERE D1.date = (
SELECT MIN(D2.date) AS MinDate
FROM D2
WHERE CHECKSUM(D1.lastName, D1.firstName, D1.birthdate, D1.gender)
= CHECKSUM(D2.lastName, D2.firstName, D2.birthdate, D2.gender)
)
It's your choice.