SQL Group and Filter

Question

I realize this is just like SQL/mysql - Select distinct/UNIQUE but return all columns? but I am at a total lose and it's always the way, report needed right away...

I am wanting to get (all from the same table)

First - Unique AsbestosNumber

Seconds - based on that the last survey date

Last - based on those filtered down results get the highest Risk (First look to see it is has 'A', then 'B' then 'C' then 'D' then anything else

select AsbestosUPRN, OverallRiskCategory, SurveyDate FROM TblAsbestos GROUP BY AsbestosUPRN, OverallRiskCategory, SurveyDate

but have also tried the below which ive of not be bound errors.

SELECT * FROM TblAsbestos  

JOIN (SELECT AsbestosUPRN, Max(OverallRiskCategory) FROM TblAsbestos) As Tbl2
On Tbl2.AsbestosUPRN = Tbl1.AsbestosUPRN   
ORDER BY  Tbl1.AsbestosUPRN

I then need to modify the report to also search by a unique number in another table.

Example date:

AsbestosUPRN SurveyDate OverallRiskCategory
011203200 2014-01-07 00:00:00.000 N/A
011203200 2014-01-07 00:00:00.000 N/A
011203200 2014-01-07 00:00:00.000 N/A
011203200 2014-01-07 00:00:00.000 C
011203200 2014-01-07 00:00:00.000 N/A
011203200 2014-01-07 00:00:00.000 N/A
030700630 2014-01-10 00:00:00.000 N/A
030700630 2014-01-10 00:00:00.000 N/A
030700620 2014-01-15 00:00:00.000 N/A
030700620 2014-01-15 00:00:00.000 N/A
030700620 2014-01-15 00:00:00.000 N/A
030700620 2014-01-15 00:00:00.000 N/A
030700620 2014-01-15 00:00:00.000 N/A
030700630 2014-01-10 00:00:00.000 N/A
030700630 2014-01-10 00:00:00.000 N/A

Fixed, working code:

select 
    AsbestosUPRN, 
    min(OverallRiskCategory) as OverallRiskCategory, 
    max(SurveyDate) as SurveyDate
FROM TblAsbestos 
GROUP BY 
    AsbestosUPRN

This might be a little more clear if you mocked up a schema with some sample data in [sqlfiddle](http://sqlfiddle.com). Make sure to note expected output. — crthompson, Sep 08 '14 at 17:22

score 1 · Answer 1 · answered Sep 08 '14 at 17:29

1

If the AsbestotosUPRN is unique, it can't be all within one table. I assume, you have following table:

UPRN    OverallRiskCategory   SurveyDate
   1                2           1/1/14
   1                5           1/2/14
   1                3           1/3/14
   2                7           1/1/14

And you want to get something like this:

UPRN    OverallRiskCategory
   1                5      
   2                7

right?

then, you should work with aggregated functions:

SELECT UPRN, max(OverallRiskCategoy)
FROM Tbl
GROUP BY UPRN

answered Sep 08 '14 at 17:29

Theo Müller

45
4

Sadly it is not Unique, that would be to easy :-( – indofraiser Sep 08 '14 at 17:31
Can you also add your expected result on the sample data? This would be helpful to understand, what you are going to achieve – Theo Müller Sep 08 '14 at 21:15

score 1 · Accepted Answer · answered Sep 08 '14 at 18:44

If I understand your question correctly you want the UPRN with the most recent survey date for the UPRN, with the highest risk classification on that UPRN/Date combination. If that is what you want then the following SQL code should work.

WITH md AS (
    SELECT AsbestosUPRN, Max(SurveyDate) AS maxdate
    FROM TblAsbestos
    GROUP BY AsbestosUPRN
    )
SELECT md.AsbestosUPRN, maxdate, Max(overallriskcategory) AS maxrisk
FROM md, TblAsbestos t
WHERE md.AsbestosUPRN = t.AsbestosUPRN AND
    md.maxdate = t.surveydate
GROUP BY md.AsbestosUPRN, maxdate;

Note that this will only work if the N/A data is actually NULL in the database. Otherwise max(overallriskcategory) will always return 'N/A' as it is the 'highest' string. If you need to convert the 'N/A' to NULL the following should work:

UPDATE tblasbestos
SET overallriskcategory = NULL
WHERE overallriskcategory = 'N/A';

Marked as answer as right. What I have done is create a numerical value for the Overallriskcategory to ensure the output is right. — indofraiser, Sep 09 '14 at 08:08

SQL Group and Filter

2 Answers2