Abhinav Piplani - 3 months ago 29

SQL Question

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;
```