Selecting Grouped Records based on max value in a column

Question

I cannot get my head around this one, I have a "funcky" subquery:

SELECT  
"Requisition_ID",  
"Candidate_ID",  
"Application_Date",   
CA."Process_of_Activity",  
CA."Activity",   
"Completed_on",  
add_minutes(cast("Completed_on" as timestamp),STAT."Rank") as "Current Completed on"  

FROM CA

left join STATUS as STAT   
on CA."Process_of_Activity" = STAT."Process_of_Activity" and CA."Activity" = STAT."Activity"  
GROUP BY "Requisition_ID","Candidate_ID","Application_Date", STAT."Rank", CA."Process_of_Activity", CA."Activity", "Completed_on";

This basically gives me a table with 7 columns. I need a result with the same 7 columns GROUPED by the first 3 where the last column is Max. For example. if the original subquery shows me:

Requisition_ID  Candidate_ID    Application_Date    Process_of_Activity Activity         Completed_on   Current Completed on
123              555            16/12/2015          To be Rejected      Rejection        08/03/2016     08/03/2016 00:29
123              555            16/12/2015          To be Rejected      Letter:Rejection 08/03/2016     08/03/2016 00:00
123              555            16/12/2015          Application Entry   Questionnaire    16/12/2015     16/12/2015 00:00
123              555            16/12/2015          Application Entry   Application Entr 08/03/2016     08/03/2016 00:01

in this case I would only want to see the first line of this sample ... so I want to group by "Requisition_ID", "Candidate_ID", "Application_Date"

but also see what the values are for: CA."Process_of_Activity", CA."Activity", "Completed_on"

where "Current Completed on" is max. but of course much more Requisitions, candidates and Application dates in the system. I am using dashDB.

Bhavesh Ghodasara · Accepted Answer · 2016-10-28T10:47:21.210

1

You can use row_number/rank/dense_rank for this purpose. You need something like following:

SELECT *
    FROM 
    (
    SELECT "Requisition_ID",
        "Candidate_ID",
        "Application_Date",
        "Process_of_Activity",
        "Completed_on",
        cco,
        ROW_NUMBER()OVER(PARTITION BY "Requisition_ID",
        "Candidate_ID",
        "Application_Date"
        ORDER BY cco DESC)rn
        FROM 
        (
        SELECT
            "Requisition_ID",
            "Candidate_ID",
            "Application_Date",
            CA."Process_of_Activity",
            CA."Activity",
            "Completed_on",
            add_minutes(CAST("Completed_on" AS TIMESTAMP),STAT."Rank") AS "cco"
            FROM CA
            LEFT JOIN STATUS AS STAT
            ON CA."Process_of_Activity" = STAT."Process_of_Activity" AND CA."Activity" = STAT."Activity"
            GROUP BY "Requisition_ID",
                "Candidate_ID",
                "Application_Date",
                STAT."Rank",
                CA."Process_of_Activity",
                CA."Activity",
                "Completed_on"
        ) AS INNER_QUERY)AS OUTER_QUERY
    WHERE rn=1

This SQL not compiled on dasdb but row_number function is important. it will give you idea.

edited Oct 28 '16 at 10:47

answered Oct 28 '16 at 10:22

Bhavesh Ghodasara

1,981
2
15
29

1

Hi, I'm getting this error: An unexpected token "QUALIFY" was found following ""Completed_on") x ". Expected tokens may include: "HAVING".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.69.56 – Mike Pala Oct 28 '16 at 10:25
@MikePala use where instead of qualify. – Bhavesh Ghodasara Oct 28 '16 at 10:26
when I use "HAVING" in place of QUALIFY I get this:Invalid use of an aggregate function or OLAP function.. SQLCODE=-120, SQLSTATE=42903, DRIVER=3.69.56 – Mike Pala Oct 28 '16 at 10:27
@MikePala use where and not having – Bhavesh Ghodasara Oct 28 '16 at 10:28
Now it's giving me this error:"Current Completed on" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.69.56 – Mike Pala Oct 28 '16 at 10:40
OK. I had to change one of the lines to : ORDER BY add_minutes(CAST("Completed_on" AS TIMESTAMP),STAT."Rank") DESC)rnk – Mike Pala Oct 28 '16 at 10:45
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/126904/discussion-between-bhavesh-ghodasara-and-mike-pala). – Bhavesh Ghodasara Oct 28 '16 at 10:47
Thank You very much – Mike Pala Oct 28 '16 at 10:55

Selecting Grouped Records based on max value in a column

1 Answers1