CODEWITHSUNDEEP
sqloracle-database
Chris D.
Chris D.

Reputation: 9

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"

Upvotes: 0

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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;

Upvotes: 2

Related Questions