Getting Distinct records with date field?

Question

Hello,

I have a query where i want to get a distinct description by the latest date entered and the descriptions ID. I can get the disctinct part but i run into trouble with trying to get the ID since im using MAX on the date. Here is the query:

SELECT DISTINCT Resource.Description, MAX(arq.DateReferred) AS DateReferred, arq.AssessmentResourceID
FROM AssessmentXResource AS arq 
INNER JOIN Resource ON arq.ResourceID = Resource.ResourceID 
INNER JOIN Assessment AS aq 
INNER JOIN [Case] AS cq ON aq.CaseID = cq.CaseID 
INNER JOIN [Plan] AS pq ON cq.CaseID = pq.CaseID ON arq.AssessmentID = aq.AssessmentID
WHERE     (pq.PlanID = 22)
GROUP BY Resource.Description, arq.AssessmentResourceID
ORDER BY Resource.Description

Im sure its simple but im not seeing it.

Answer 1

I doni't see a reason for the join to the [Case] table, so I've left that out. You can add it back if you need it for some reason.

SELECT
     RES.Description,
     ARQ.DateReferred,
     ARQ.AssessmentResourceID
FROM
     AssessmentXResource ARQ
INNER JOIN Resource ON
     ARQ.ResourceID = RES.ResourceID
INNER JOIN Assessment AQ ON
     AQ.AssessmentID = ARQ.AssessmentID
INNER JOIN [Plan] PQ ON
     PQ.CaseID = AQ.CaseID
LEFT OUTER JOIN AssessmentXResource ARQ2 ON
     ARQ2.ResourceID = ARQ.ResourceID AND
     ARQ2.DateReferred > ARQ.DateReferred
WHERE
     PQ.PlanID = 22 AND
     ARQ2.ResourceID IS NULL

This may not act as hoped if there are identical DateReferred values for the same ResourceID in your data. You should come up with a business rule for that situation and change the query appropriately for it. Also, this will act slightly different from your query if it's not guaranteed that you have matching rows in the Assessment, Plan, and Case tables for your AssessmentXResource rows. You can make it work by adding in joins to ARQ2 for those, but that will affect performance and is also a little more complex. If you need that then post a comment and I can alter the query to handle it or maybe you can figure it out on your own.

Answer 2

SELECT
    Resource.Description,
    arq.DateReferred AS DateReferred,
    arq.AssessmentResourceID
FROM
    Resource
INNER JOIN
    AssessmentXResource AS arq 
        ON arq.ResourceID = Resource.ResourceID 
        AND arq.DateReferred = (
                                SELECT
                                    MAX(DateReferred)
                                FROM
                                    AssessmentXResource
                                WHERE
                                    ResourceID = Resource.ResourceID
                               )
INNER JOIN
    Assessment AS aq
        ON arq.AssessmentID = aq.AssessmentID
INNER JOIN
    [Case] AS cq
        ON aq.CaseID = cq.CaseID 
INNER JOIN
    [Plan] AS pq
        ON cq.CaseID = pq.CaseID
WHERE
    (pq.PlanID = 22)
ORDER BY
    Resource.Description

Answer 3

Short answer: max function wont work if you are grouping by ids. You will end up returning the Max value for each distinct id. i.e. everything

You may need to alter the subquery representation, but you get the idea.

select id, val from table where val = (select max(val) from table)