Abhinav Piplani - 1 year ago 92

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).

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

Answer Source

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

Recommended from our users: **Dynamic Network Monitoring from WhatsUp Gold from IPSwitch**. ** Free Download**