In SQL how to populate lowest value from the list-CodePudding

From below table if an Asbesto Survey id have multiple ASbestos Status Lines then i need to populate the lowest value as Asbestos Status on line Level like below.

I have used left join and case statement but its not working.

CodePudding user response：

Left join and a window function will help you solve your problem.

Consider below query which assuming sample and rank are tables in your screenshot respectively.

SELECT s.*,
       LAST_VALUE(Asbestos_Status) OVER (
         PARTITION BY UPRN, Asbesto_survey_id ORDER BY rank RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS Asbestos_Status_on_line_level
  FROM sample s LEFT JOIN rank r ON s.Asbesto_status_lines = r.Asbestos_Status

CodePudding user response：

I have assigned ranking to each status inner joined with main table Lines table and fetch the max rank

SELECT [Asbestos Survey ID],MAX(RANKING) AS ASBESTOS_STATUS_RANK
FROM 
(SELECT DISTINCT [Asbestos Survey ID],AsbestosStatus,x.RANKING
from table1 a -- above first table.
INNER JOIN 
( SELECT 'Removed' AS AsbestosStatus, 6 AS RANKING
                UNION
                SELECT 'No Asbestos Detected' AS AsbestosStatus, 5 AS RANKING
                UNION
                SELECT 'Presumed No Access' AS AsbestosStatus, 4 AS RANKING
                UNION
                SELECT 'Presumed'  AS AsbestosStatus, 3 AS RANKING
                UNION
                SELECT 'Strongly Presumed' AS AsbestosStatus, 2 AS RANKING
                UNION
                SELECT 'Confirmed' AS AsbestosStatus, 1 AS RANKING
                ) X ON X.AsbestosStatus = A.AsbestosStatus
) Y
GROUP BY SURVEYID

So above i would get SurveyID and Max rank on the survey id and i will populate that max survey id based on Table 2.