Abhinav Piplani Abhinav Piplani - 1 month ago 15
SQL Question

Get unique values in table by multiple criteria

My table is :

Table 1

ID Payment_type Time
A X 2014 01
A Y 2014 08
B X 2013 10
A Y 2014 08
B Z 2013 09
A Y 2012 01
A Z 2014 08


And Results should be

ID Payment_type
A Y
B X


The requirement is first look at the max time for an ID. If there is only 1 observation, then get the corresponding value for payment type. If there is more than 1 row for max time for an ID, get the payment type which occurs the most (in case of tie, pick any value).

Answer

To solve this question, you need to frequency of each value at each time:

select id, payment_type, time, count(*) as cnt
from t
group by id, payment_type, time;

Next, you need to choose the maximum value for each id based on time and then cnt. The simplest method uses row_number():

select id, payment_type, time
from (select id, payment_type, time, count(*) as cnt,
             row_number() over (partition by id order by time desc, cnt desc) as seqnum
      from t
      group by id, payment_type, time
     ) ipt
where seqnum = 1;
Comments