Chris D. Chris D. - 2 months ago 7
SQL Question

How can I return rows that have the latest date?

My query returns data such as:

ID | ProcessID | Type| Value | Date
--------------------------------------------------
1 | 2 | A | 10 | 2/23/2016 10:10:42 AM
1 | 2 | B | 20 | 2/23/2016 10:10:42 AM
1 | 2 | C | 30 | 2/23/2016 10:10:42 AM
1 | 1 | A | 11 | 1/6/2016 12:48:04 PM
1 | 1 | B | 21 | 1/6/2016 12:48:04 PM
1 | 1 | C | 31 | 1/6/2016 12:48:04 PM
2 | 4 | A | 10 | 7/21/2016 11:28:22 AM
2 | 4 | B | 20 | 7/21/2016 11:28:22 AM
2 | 4 | C | 30 | 7/21/2016 11:28:22 AM
2 | 3 | A | 11 | 6/21/2016 09:41:07 AM
2 | 3 | B | 21 | 6/21/2016 09:41:07 AM
2 | 3 | C | 31 | 6/21/2016 09:41:07 AM


However, I really only care to get the most recent data for Type's A, B, and C. Such that my return would look like this:

ID | ProcessID | Type| Value | Date
--------------------------------------------------
1 | 2 | A | 10 | 2/23/2016 10:10:42 AM
1 | 2 | B | 20 | 2/23/2016 10:10:42 AM
1 | 2 | C | 30 | 2/23/2016 10:10:42 AM
2 | 4 | A | 10 | 7/21/2016 11:28:22 AM
2 | 4 | B | 20 | 7/21/2016 11:28:22 AM
2 | 4 | C | 30 | 7/21/2016 11:28:22 AM


I've tried using max in the select clause but that is not working.

Here is my sql, limited for the sake of example to one "ID", along with a sample of the actual return:

select f.folderrsn,fpi.processrsn,fpi.infocode, fpi.infovalue, fp.enddate
from folderprocessinfo fpi
join folderprocess fp on fp.processrsn = fpi.processrsn
join folder f on f.folderrsn = fp.folderrsn
where fpi.infocode in (51437,51438,51439)
and trunc(f.issuedate) > '01-JAN-16'
and not fpi.infovalue is null
and f.folderrsn = 11179547 --limited to one "ID" for example
order by fp.enddate asc


Return:

"FOLDERRSN","PROCESSRSN","INFOCODE","INFOVALUE","ENDDATE"
"11179547","37159700","51437","91","6/2/2015 10:27:46 AM"
"11179547","37159700","51438","0","6/2/2015 10:27:46 AM"
"11179547","37159700","51439","145.5","6/2/2015 10:27:46 AM"
"11179547","37540455","51437","91","7/24/2015 6:44:08 AM"
"11179547","37540455","51438","0","7/24/2015 6:44:08 AM"
"11179547","37540455","51439","145.5","7/24/2015 6:44:08 AM"

Answer

You can use row_number():

with f as (
      select f.folderrsn, fpi.processrsn, fpi.infocode,
             fpi.infovalue, fp.enddate
      from folderprocessinfo fpi join
           folderprocess fp
           on fp.processrsn = fpi.processrsn join
           folder f
           on f.folderrsn = fp.folderrsn
      where fpi.infocode in (51437, 51438, 51439) and
            trunc(f.issuedate) > '01-JAN-16' and
            fpi.infovalue is not null
     )
select f.*
from (select f.*,
             row_number() over (partition by processid, type order by enddate desc) as seqnum
      from f
     ) f
where seqnum = 1
order by enddate asc;
Comments