How to filter MySQL results to display LIMIT 1 on per user basis?

Question

Here is a screenshot of the SQL command results:

Here is the SQL command:

SELECT inl_cbsubs_subscriptions.user_id AS cbsubsUserID, inl_cbsubs_subscriptions.id AS cbsubsId, inl_cbsubs_subscriptions.status AS status, inl_cbsubs_payment_items.subscription_id     AS paymentSubID, inl_cbsubs_payment_items.stop_date AS paymentStopDate, inl_cbsubs_payment_items.id AS paymentID
FROM inl_cbsubs_subscriptions
INNER JOIN inl_cbsubs_payment_items
ON inl_cbsubs_subscriptions.id=inl_cbsubs_payment_items.subscription_id
WHERE status='C'
ORDER BY paymentID DESC;

I am looking to adjust this command so that I have only the most recent result showing on a per user basis. So in other words, this is what the table should resemble in this case:

As you can see, the cbsubsUserID only shows one result per ID whereas before there were multiple results for the 596 id.

Answer 1

If you want the most recent result, the best way to do it is with not exists:

SELECT s.user_id AS cbsubsUserID, s.id AS cbsubsId, s.status AS status,
       i.subscription_id AS paymentSubID, i.stop_date AS paymentStopDate, i.id AS paymentID
FROM inl_cbsubs_subscriptions s INNER JOIN
     inl_cbsubs_payment_items i
     ON s.id = i.subscription_id
WHERE s.status = 'C' and
      not exists (select 1
                  from inl_cbsubs_payment_items i2
                  where i2.subscription_id = i.subscription_id and
                    i2.id > i.id
                 )
ORDER BY paymentID DESC;

You will want an index on inl_cbsubs_payment_items(subscription_id, paymentid).

What this is saying is: "Get me all the items for a given subscription id that have no bigger payment stop dates for that subscription". It is a fancy way of saying "get me the most recent for each subscription id", but it tends to work best in the database.