1

I've looked through the greatest-n-group-by group and can't find this answer and can't make the JOIN solutions work with the WHERE CLAUSE. Here's what I've got.

t1 table:

ID  Product_Name    Quantity    pharmacyShort   Copay_Amount
581284  AMLODIPINE 10 MG    30  WALGREENS   21.07
581283  AMLODIPINE 10 MG    30  CVS 36.61
581282  AMLODIPINE 10 MG    28  RITE AID    30.98
581280  AMLODIPINE 10 MG    60  WALGREENS   50.65
581279  AMLODIPINE 10 MG    30  CVS 29.78
581278  AMLODIPINE 10 MG    30  RITE AID    14.28
581277  AMLODIPINE 10 MG    180 WALGREENS   33.83
581276  AMLODIPINE 10 MG    15  CVS 18.33
581275  AMLODIPINE 10 MG    10  RITE AID    45.93
581274  AMLODIPINE 10 MG    30  PUBLIX  33.75

I also have a priority table, call it t2, on which I'd like the output prioritized (sorted) by:

id  pharmacyShort   COUNT
1   CVS 100
2   RITE AID    99
3   TARGET  98
4   WALGREENS   97
5   WALMART 96
6   KMART   95
7   KROGER  94
8   PUBLIX  93

MYSQL:

SELECT t1.pharmacyShort , t1.Copay_Amount 
FROM `t1` 
INNER JOIN `t2` 
ON t1.pharmacyShort = t2.pharmacyShort 
WHERE t1.Product_Name = 'AMLODIPINE 10 MG' 
AND t1.Quantity = '30' 
AND t1.ID > 555000 
GROUP BY t1.pharmacyShort 
ORDER BY t2.COUNT DESC LIMIT 30

I have the ID > 555000 to keep the query time low. Also, I'm trying to show the most recent prices. Perhaps that can be done with an ORDER BY but the challenge is that some drugs have more claims while others have much less which means I have to go further back in time to get a price.

What I'm hoping to get is this (I actually only need the pharmacyShort and Copay_Amount columns on the output, but left the other columns for clarity here):

581283  AMLODIPINE 10 MG    30  CVS 36.61
581284  AMLODIPINE 10 MG    30  WALGREENS   21.07
581278  AMLODIPINE 10 MG    30  RITE AID    14.28
581274  AMLODIPINE 10 MG    30  PUBLIX  33.75

The output should pick only the CVS with ID 581283 and show just the one claim from CVS but instead (as I've learned) GROUP BY is picking the older claim, 581279, like this:

581279  AMLODIPINE 10 MG    30  CVS 29.78
581284  AMLODIPINE 10 MG    30  WALGREENS   21.07
581278  AMLODIPINE 10 MG    30  RITE AID    14.28
581274  AMLODIPINE 10 MG    30  PUBLIX  33.75

If it makes it easier, I can live without the priority table. I've also considered trying to take care of this on the PHP side by some kind of array sort, but I think MYSQL would be faster.

Many, many thanks and good karma to any and all that can help.

UPDATE:

Based on the answers I've gotten, here is where I am:

SELECT a.pharmacyShort, a.copay_amount
FROM `t1` a
JOIN (SELECT MAX(ID) as maxid, pharmacyShort
FROM `t1`
WHERE Product_Name = 'AMLODIPINE 10 MG' 
      AND Quantity = '30' 
      GROUP BY pharmacyShort) AS maxt1
  ON a.pharmacyShort = maxt1.pharmacyShort AND a.id = maxt1.maxid
join t2 b ON a.pharmacyShort = b.pharmacyShort
ORDER BY b.count DESC
LIMIT 20

I removed from the WHERE clause the id > 550000 because using the MAX(ID) I don't think I'll need it.

But I'm getting a zero result set. So where am I going wrong?

Dave Smith
  • 13
  • 1
  • 5
  • How do you determine "most recent price"? There's no date column. – Barmar Jan 17 '18 at 17:55
  • Write a subquery to get the most recent price, using one of the techniques in the question I linked to. Then join that with the priority table. – Barmar Jan 17 '18 at 17:59
  • Its a different question because I'm not just looking for the NULL produced by that JOIN. I need to whittle down to the drug, quantity and pharmacy. Where does that go in the question you referenced? At the end? Inside the inner join? It seems as though all those examples, the top question/answer sets in this group. all are looking for the highest or latest X. I'm looking for the most recent X given (WHERE) A, B, C and D – Dave Smith Jan 17 '18 at 18:20
  • Sadly, my dates are varchar so not very useful. IDs, however, being sequential, give me some degree of indication of WHEN they were entered and I can work with that. – Dave Smith Jan 17 '18 at 18:21
  • What NULL are you talking about? – Barmar Jan 17 '18 at 19:39
  • You put the `WHERE` clause in the subquery that gets the max ID for each group. – Barmar Jan 17 '18 at 19:42
  • Under the link you provided...under the heading "Left Joining with self, tweaking join conditions and filters". #2 – Dave Smith Jan 17 '18 at 20:33
  • I've tried reconstructing his query with mine and it's failing. I've fiddled with where I'm putting the group by and order by but none work. SELECT a.ID, a.pharmacyShort, a.Product_Name, a.Copay_Amount FROM `t1` a INNER JOIN ( SELECT `ID`, `Product_Name`, `pharmacyShort` , `Copay_Amount` FROM `t1` WHERE `Product_Name` = 'AMLODIPINE 10 MG' ) b ON a.Product_Name = b.Product_Name GROUP BY `pharmacyShort` ORDER BY a.ID DESC – Dave Smith Jan 17 '18 at 20:34
  • I've never used that method, I always use the one before it. But if you use that method, I think you need to put the conditions in both the main query for table `a`, and the `ON` clause for table `b`. – Barmar Jan 17 '18 at 20:35

1 Answers1

1

Use the Joining with simple group-identifier, max-value-in-group Sub-query method at SQL select only rows with max value on a column and put the conditions in the sub-query.

Then join this with t2 to get the count column for ordering.

SELECT t1.pharmacyShort, t1.copay_amount
FROM t1
JOIN (SELECT pharmacyShort, MAX(id) AS maxid
      FROM t1
      WHERE Product_Name = 'AMLODIPINE 10 MG' 
      AND Quantity = '30' 
      AND ID > 555000
      GROUP BY pharmacyShort) AS maxt1
    ON t1.pharmacyShort = maxt1.pharmacyShort AND t1.id = maxt1.maxid
JOIN t2 on t1.pharmacyShort = t2.pharmacyShort
ORDER BY t2.count DESC
LIMIT 20
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Okay, Barmar, working my way through this. The first step you outline...WORKS! I know, I sound shocked and probably shouldn't be, but I've been working on this for hours and hours so this is progress. Next up, step 2...thanks!! `SELECT a.ID, a.pharmacyShort FROM t1 a INNER JOIN ( SELECT pharmacyShort, MAX( id ) id FROM t1 GROUP BY pharmacyShort ) b ON a.pharmacyShort = b.pharmacyShort AND a.id = b.id LIMIT 150 , 30` – Dave Smith Jan 18 '18 at 14:10
  • Check that! My Update in the top post actually WORKS! IT WORKS! IT WORKS! IT WORKS! Gosh, what a monkey off my back. Thank you Barmar! From frustration to glee...much appreciated! – Dave Smith Jan 18 '18 at 16:31
  • You should edit the question to remove the update that says it doesn't work. Your query appears to be the same as the answer, so no need to repeat it in the question. – Barmar Jan 18 '18 at 18:05