CODEWITHSUNDEEP
sqlgoogle-bigquery
Simon Breton
Simon Breton

Reputation: 2876

How do I return max value in a non-aggregated sql query based on another column value

I've recently learned how to return max value using ARRAY_AGG function with aggregated query ( by aggregated I mean when there is a group by function at some point in the query). I recently tried to apply the ARRAY_AGG function with a non-aggregated query to also return max value and It didn't work as I was expecting. So my question is : how do I get the max value of a column based on another column input.

To add more context to my question here is the situation. Let say you have a user who is updating his status. I want a query where I can have is most recent status but at the same time his second most recent one (or the one before his most recent update).

so here is the table :

    accountKey  paymentType   date          Status   
    51135473    transaction   2016-10-16    Mini         
    51135473    UpdateStatus  2016-09-22    Mini         
    51135473    transaction   2016-12-08    Standard         
    51135473    transaction   2016-11-08    Standard         
    51135473    UpdateStatus  2016-11-08    Standard

Here is the result I want :

accountKey  paymentType   date       Status   CurrentStatus  PreviousStatus 
51135473    transaction   2016-10-16 Mini     Standard       Mini
51135473    UpdateStatus  2016-09-22 Mini     Standard       Mini
51135473    transaction   2016-12-08 Standard Standard       Mini
51135473    transaction   2016-11-08 Standard Standard       Mini
51135473    UpdateStatus  2016-11-08 Standard Standard       Mini

Last status is simply the very last status in set. The previous status is the status very the current one. The global idea is to have for each UpdateStatus paymentType at the same time the current status and the previous one.

Like I said I've tried to use the following query :

SELECT
  accountKey,
  paymentType,
  date,
  status,
  ARRAY_AGG(status ORDER BY date DESC LIMIT 1)[OFFSET (0)] CurrentStatus
FROM
  Table
GROUP BY 
  accountKey,
  paymentType,
  receivedOn

However this query makes little sense here since I don't need to group anything. I'm grouping here because otherwise I can run the query. I understand that I have to add Group by function because I'm using ARRAY_AGG. So my question again, how to I replace the ARRAY_AGG function I'm using here in a non-grouped query.

Hopefully all this will make sense.

Thanks.

Upvotes: 0

Views: 138

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

You can use nth_value():

select t.*,
       nth_value(status, 1) over (partition by accountkey order by date desc) as current_status,
       nth_value(status, 2) over (partition by accountkey order by date desc) as current_status_but_one
from t;

If you just wanted the current status, I would recommend first_value(). But you are looking for multiple statuses, so you might as well use the same function for all of them.

Upvotes: 2

Related Questions