CODEWITHSUNDEEP
sqlsql-server-2005
indofraiser
indofraiser

Reputation: 1024

SQL Group and Filter

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

Upvotes: 0

Views: 65

Answers (2)

Gregory Arenius
Gregory Arenius

Reputation: 3204

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';

Upvotes: 1

Theo Müller
Theo Müller

Reputation: 45

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

Upvotes: 1

Related Questions