Selecting values in columns based on other columns

Question

I have two tables, info and transactions. info looks like this:

customer ID Postcode 1 ABC 123 2 DEF 456

and transactions looks like this:

customer ID day frequency 1 1/1/12 3 1 3/5/12 4 2 4/6/12 2 3 9/9/12 1

I want to know which day has the highest frequency for each postcode.

I know how to reference from two different tables but im not too sure how to reference multiple columns based on their values to other columns.

The output should be something like this:

customer ID postcode day frequency 1 ABC 123 3/5/12 4 2 DEF 456 4/6/12 2 3 GHI 789 9/9/12 1

and so on.

Answer 1

You can filter with a correlated subquery:

select
    i.*,
    t.day,
    t.frequency
from info i
inner join transactions t on t.customerID = i.customerID
where t.frequency = (
    select max(t.frequency)
    from info i1
    inner join transactions t1 on t1.customerID = i1.customerID
    where i1.postcode = i.postcode
)

Or, if your RBDMS supports window functions, you can use rank():

select *
from (
    select
        i.*,
        t.day,
        t.frequency,
        rank() over(partition by i.postcode order by t.frequency desc)
    from info i
    inner join transactions t on t.customerID = i.customerID
) t
where rn = 1