Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers 3

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.

share|improve this answer
add comment

Try this one:

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

...

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.