SQL Latest date

Question

I thought I had it but it seemed I was wrong.

I have a query

select PrestatieCode, Uitvoerdatum, Identificatie, Foutmelding 
from bam_Zending_AllInstances as zAll
left outer join bam_Zending_CompletedRelationships as zRel on 
zAll.ActivityID = zRel.ActivityID
left outer join bam_Prestatie_AllInstances as pAll on 
zRel.ReferenceData = pAll.ActivityID
where zAll.Zender = 'BL15' 
and ReferenceType = 'Activity' 
and Zendingnummer = '632'

This shows a list of PrestatieCodes, also duplicates. Now I only need to have a list of PretatieCodes without the duplicates, it should just pick the one with the lastest date. The datecolumn = Uitvoerdatum.

I thought I'd try something like:

select max(Uitvoerdatum), PrestatieCode  
from bam_Zending_AllInstances as zAll
left outer join bam_Zending_CompletedRelationships as zRel on 
zAll.ActivityID = zRel.ActivityID
left outer join bam_Prestatie_AllInstances as pAll on 
zRel.ReferenceData = pAll.ActivityID
where zAll.Zender = 'BL15' 
and ReferenceType = 'Activity' 
and Zendingnummer = '632'
group by PrestatieCode

which gives me what I want. However as you can notice it does not have the 2 columns "Identificatie" and Foutmelding. If I add these it gives me the error

Column 'bam_Prestatie_AllInstances.Identificatie' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So I'm guessing my approach is wrong. I need to see all the columns but only for the latest "PrestatieCode"s.

Am I making any sense?

Answer 1

In your second query, you're saying that you want one row for each unique PrestatieCode. So a result set of possibly several rows with the same PrestatieCode will be merged into one row. The value of PrestatieCode is given, because it is the same across all of these rows (if we're considering one particular group). The value of Uitvoerdatum is also easy: you're telling the server to just pick the max one. What about Identificate, then? It could be of several different values, and you haven't specified which one to pick. If it doesn't matter, you could do max(Identificate) as well. If you know that they're always gonna be the same, for all PrestatieCodes that are the same, you can safely throw that value into the group by clause as well.

Whatever you decide on, you need to give the sql server some clues regarding which values you're interested in.

Answer 2

Without any schema it is a tad difficult. But what you need to try is getting the id and max value (so in your case PrestatieCode and MAX(Uitvoerdatum) and then do a sub join)

SELECT  YourTabel.*
FROM    YourTabel INNER JOIN
     (
      SELECT PrestatieCode,
        MAX(Uitvoerdatum) MaxDates
      FROM YourTabel
      WHERE YourCondition
      GROUP BY PrestatieCode 
     ) YourTableMaxDates ON YourTabel.PrestatieCode = YourTableMaxDates.PrestatieCode
          AND YourTabel.Uitvoerdatum = YourTableMaxDates.MaxDates

This should get you the latest data per PrestatieCode

Answer 3

You can also put the second one (simplified as much as possible since you only want the two columns) in a subquery in the "from" clause, give it a name, and use it in a join to limit the output of the first query to the line having the values from the second query.

Answer 4

I think your question has been answered before here. You'll notice that the best solution depends on what server you are using, since many have special functionality to improve these queries.