jquerysqlsql-servermindate

How to hide MIN(date) "aggregate" from SELECT row in the query output...SQL Server 2008


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

Solution

  • 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.