MySQL Query - How to get most recent demographics?

Question

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.

Answer 1

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.

Answer 2

Try this one:

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

...

Answer 3

There are several ways for this type of query:

SELECT 
    c.*
FROM 
        cards AS c
    JOIN
        ( SELECT 
              cardnbr
            , MAX(lastvisit) AS lastvisit
          FROM 
              cards
          GROUP BY 
              cardnbr
        ) AS maxc
      ON  maxc.cardnbr = c.cardnbr
      AND maxc.lastvisit = c.lastvisit ;

You can also use the primary key for this solution:

SELECT 
    c.*
FROM 
        ( SELECT DISTINCT
              cardnbr
          FROM
              cards
        ) AS dc
    JOIN
        cards AS c
      ON  c.PK =                           --- the Primary Key here
          ( SELECT 
                cc.PK                      --- and here
            FROM 
                cards AS cc
            WHERE 
                cc.cardnbr = dc.cardnbr
            ORDER BY 
                cc.lastvisit DESC
              LIMIT 1
          ) ;

An index on (cardnbr, lastvisit, PK) will help either of the two queries.