How to get the Max Date, Min Date and Null is the column is empty using SQL Query-CodePudding

I have some trouble in creating an SQL query. I want query the records in my table with all columns and rows.

I use the below select query. It actually reflects the records that I want, but the only problem is it only shows 3 column out of the 19 column. It only shows the column of Barangay, Wentoff and Restored. What I want is to show all the 19 columns of the corresponding result.

This is my SQL Code:

SELECT Barangay, MIN(wentoff) as Wentoff, 
CASE WHEN MAX(CASE WHEN Restored IS NULL THEN 1 ELSE 0 END) = 0 THEN MAX(Restored) End 
FROM ReportforValidation 
GROUP BY Barangay

Please see the current result of my query:

This is the result of the query

The fields of the the table that I need to show:

The field of the the table that need to show

CodePudding user response：

You can use window functions instead of group by:

SELECT  Barangay, 
        MIN(wentoff) OVER (
            PARTITION BY Barangay
        ) as Wentoff, 
        FIRST_VALUE(Restored) OVER (
            PARTITION BY Barangay 
            ORDER BY CASE WHEN Restored IS NULL THEN 0 ELSE 1 END, 
                     Restored DESC
        ),
        ReportNo,
        area,
        calamaty,
        response
FROM    ReportforValidation;

Add all the columns you want to have in the output to the select clause.

If you don't want all the rows, but only those that have the "first value" of Restored, then also select the partition's row number and wrap that in a select query that filters for row number 1:

SELECT *
FROM   (
    SELECT  Barangay, 
            MIN(wentoff) OVER (
                PARTITION BY Barangay
            ) as Wentoff, 
            FIRST_VALUE(Restored) OVER (
                PARTITION BY Barangay 
                ORDER BY CASE WHEN Restored IS NULL THEN 0 ELSE 1 END, 
                         Restored DESC
            ) Restored,
            ROW_NUMBER() OVER (
                PARTITION BY Barangay 
                ORDER BY CASE WHEN Restored IS NULL THEN 0 ELSE 1 END, 
                         Restored DESC
            ) as rn,
            ReportNo,
            area,
            calamaty,
            response
    FROM    ReportforValidation
    ) as all_rows
WHERE rn = 1;

CodePudding user response：

@trincot sir your codes works perfectly my only concern with this code is that the rn is also appearing in the column. how can i hide it?
SELECT * FROM ( SELECT Barangay, MIN(wentoff) OVER ( PARTITION BY Barangay ) as Wentoff, FIRST_VALUE(Restored) OVER ( PARTITION BY Barangay ORDER BY CASE WHEN Restored IS NULL THEN 0 ELSE 1 END, Restored DESC ) Restored, ROW_NUMBER() OVER ( PARTITION BY Barangay ORDER BY CASE WHEN Restored IS NULL THEN 0 ELSE 1 END, Restored DESC ) as rn, ReportNo, area, calamaty, response FROM ReportforValidation ) as all_rows WHERE rn = 1;