SQL query to find largest id for each user_id

I am currently querying from 4 different tables,

users, memberships_users, membership_levels, phone_area_code

From the users table, I would like to select

id
user_login,
user_email,
display_name

The memberships_users table contains a user_id column as well as an id column. The user_id column is not unique, and there are many repeating user ids.

From the memberships_users table, I would like to select

initial_payment,
billing_amount,
cycle_period,
cycle_number,

from the row with the largest id for each user_id

and

name from the membership_levels table and

meta_value from the phone_area_code table

which should all relate the id in the users table

Answer

This is basically pseudo-code of one way of getting what you want.

First, create a derived table of each user_id and the max(id) from the membership table. This will yield all distinct user_id’s aligned with the associated max id from the membership. The next step is to join the two physical tables with the derived one to get the user and the associated membership record contain the max membership id.

NOTE : If your memberships_users table is on the large side then you will get better performance dropping the max_membership_id data into a temp table and adding appropriate indexes.

;WITH max_membership_id AS
(
    SELECT user_id, max_id = MAX(id)
    FROM memberships_users
    GROUP BY user_id            
)
SELECT 
    u.id, u.user_login, u.user_email, u.display_name,
    mu.initial_payment, mu.billing_amount, mu.cycle_period, mu.cycle_number
FROM
    users u
    INNER JOIN max_membership_id mmi ON mmi.user_id = u.user_id
    INNER JOIN memberships_users mu ON mu.id = mmi.max_id