1

I have a table named members with the following fields:

  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `member_id` CHAR(9) NOT NULL,
  `contact` VARCHAR(255) NULL DEFAULT NULL,
  `active_date` DATE NULL DEFAULT NULL

There can be multiple records with the same member_id, and I have been using the following query to get the record I am looking for:

  select * from members 
  where member_id=? 
  order by case when contact is not null then 1 else 0 end desc, active_date desc, id desc limit 1;

This works fine but now I am trying to put together a query that will return the above row for every unique member_id so I don't have to query each member_id individually and so far have been unsuccessful. I looked at the answers to SQL select only rows with max value on a column but it doesn't address how to handle the grouping based on the order by clause.

Any suggestions?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Wharz
  • 11
  • 2
  • I added the [tag:greatest-n-per-group] tag. This type of question, and its many variations, has been answered frequently on Stack Overflow. Take a look at some other highly-upvoted answers under that tag for other suggestions. – Bill Karwin Sep 23 '21 at 20:38

0 Answers0