MySQL get one record per group

Question

I have a MySQL table targeted_refills with columns: id (unique, primary key), pat_name, pat_phone, rx_number, drug_name, qty_disp, last_date, qty_left, price_code, last_price, last_contact, doc_name, ehf, userid

What I'm trying to do is list one record for each userid. The record I want listed is the most recent one based on the field last_date however if there is more than one record for that user with the most recent date then I want it to select the field with the largest value in the field last_price. In the rare event that there are two records with matching last_date and last_price (shouldn't happen but is possible), I really don't care which is chosen so it can be chosen off the largest value of id since that is unique and the primary key.

EDIT:

I've tried this query that I pieced together from another person's question:

SELECT t1.*
FROM `targeted_refills` AS t1
LEFT OUTER JOIN `targeted_refills` AS t2
  ON t1.userid = t2.userid 
        AND (t1.last_date < t2.last_date 
         OR (t1.last_date = t2.last_date AND t1.last_price < t2.last_price))
WHERE t2.userid IS NULL;

and it does a reasonable job of getting where I want to go, but it's pulling a few more records than I want. I'm comparing now to see what extra records it's pulling and why. It also takes a long time to run.

You should you use a subquery, do you have a `SELECT` statement as a starting point? — Jérôme, Jun 14 '13 at 19:55
[SQL Select only rows with Max Value on a Column](http://stackoverflow.com/q/7745609/1037210). — Lion, Jun 14 '13 at 19:55
possible duplicate of [Fetch the row which has the Max value for a column](http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — Bill Karwin, Jun 14 '13 at 19:55
[So what have you tried so far ?](http://mattgemmell.com/2008/12/08/what-have-you-tried) — Prix, Jun 14 '13 at 21:17

peterm · Accepted Answer · 2013-06-14T22:59:33.307

0

UPDATED You can tweak your existing query a little bit: you need at least one more join condition that prevent duplicates when both last_date and last_price are equal across several rows. Since you have auto-generated id you can use it to differentiate rows.

SELECT t1.*
  FROM targeted_refills t1 LEFT JOIN targeted_refills t2
    ON t1.userid = t2.userid 
   AND (
         t1.last_date < t2.last_date 
         OR 
         (t1.last_date = t2.last_date AND 
          t1.last_price < t2.last_price)
         OR
         (t1.last_date = t2.last_date AND 
          t1.last_price = t2.last_price AND
          t1.id < t2.id)         
       )
WHERE t2.userid IS NULL;

Or you can achieve your goal another way

SELECT id, pat_name, pat_phone, rx_number, drug_name, 
       qty_disp, last_date, qty_left, price_code, 
       last_price, last_contact, doc_name, ehf, userid
  FROM 
(
  SELECT r.*,
         @n := IF(@u = userid, @n + 1, 1) rownum,
         @u := userid
    FROM targeted_refills r
   ORDER BY userid, last_date DESC, last_price DESC
) q
 WHERE q.rownum = 1

Here is SQLFiddle demo for both queries.

edited Jun 14 '13 at 22:59

answered Jun 14 '13 at 22:42

peterm

91,357
15
148
157

Thanks. They both seem to work (I changed some of the data in the SQLFiddle demo to match what I was trying to accomplish and the format of my data in case it helps anyone else) [SQLFiddle](http://sqlfiddle.com/#!2/71c06/1). It runs really slowly for me as it sifts through about 20,000 records. It takes several minutes to get through it all. It's on a test server though so I'll try it out on a "real" one to see how it does. Either way though, the few minutes of waiting is way better than the time it takes me to do it manually. Thanks again. – user1678042 Jun 15 '13 at 04:59
@user1678042 I'm glad I could help :). Make sure that you have indices on `last_date` and `last_price`. That may help to speed things up. – peterm Jun 15 '13 at 05:14
DOH!!! That's usually one of my first steps but I completely forgot the indices. Now it's seconds instead of minutes. I literally face palmed. Thanks again!!! – user1678042 Jun 15 '13 at 13:54

MySQL get one record per group

1 Answers1