MySQL Query – How to get most recent demographics

MySQL

I have a table that has duplicated cardnbr's. I need to pull out demographics that are the most recently used by the customer. I have a date field (lastvisit) that shows their most recently used card. Here's an example query that doesn't work:

SELECT cardnbr, fname, lname, address, city, state, postal
FROM cards 
WHERE lastvisit = MAX( lastvisit )
GROUP BY cardnbr

How can I format my query to do this but actually work?

Also, the primary key for this table was created after duplicated data was added to the table.

Best Answer

Assuming you have a PRIMARY KEY or otherwise UNIQUE KEY called id (replace with whatever it's called in your table), consider the following query:

SELECT
  cardnbr,
  fname,
  lname,
  address,
  city,
  state,
  postal
FROM
  cards,
  (
    SELECT 
      GROUP_CONCAT(top_id_per_group) AS top_ids
    FROM
      (
        SELECT 
          SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY lastvisit DESC), ',', 1) AS top_id_per_group
        FROM
          cards
        GROUP BY
          cardnbr
      ) s_top_ids_per_group
  ) s_top_codes
WHERE
  FIND_IN_SET(id, top_ids)
ORDER BY
  cardnbr
;

The idea is to get, per cardnbr, the id where the lastvisit is most recent. Then, get all row data for those ids.

The above is a simplification of Selecting Top N Records Per Group, where N = 1.

Another way to solve it is described in Selecting Non Aggregated Column Data in GROUP BY. The query presented in this post actually provides better performance than the above, but uses some parsing and casting to get the final results (all columns parsed as strings, then cast back to their original types). Also see this post. Apologies for the many links, but each post presents with a different solution to the problem.

Related Question