0

I have a table with columns PredCustId, StartDT and EndDT. For a given StartDT, there can be multiple PredCustIds. Here's what this looks like

enter image description here

For each unique StartDT, I would like to retrieve the row with the largest PredCustId. I am specifically trying to implement the left-join solution as seen here but the query hangs every time I run it and I don't understand why.

This works

SELECT a.*
  FROM PredCusts AS a
LEFT OUTER JOIN PredCusts AS b
  ON a.StartDT = b.StartDT;

but this hangs

SELECT a.*
  FROM PredCusts AS a
LEFT OUTER JOIN PredCusts AS b
  ON a.StartDT = b.StartDT AND a.PredCustsId < b.PredCustsId;

Why? Note that I am using MySQL 5.7.21 and MySQL Workbench 6.3.

EDIT My table has ~370,000 rows. The only index is the Primary Key, PredCustsId.

Ben
  • 20,038
  • 30
  • 112
  • 189

1 Answers1

1

You could use a inner join on a subquery for max value 

select * from PredCusts p
inner join (
  select StartDT, max(PredCustId) max_precustid
  from PredCusts
  group by StartDT
) t on t.StartDT = p.StartDT and p.PredCustId = t.max_precustid
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Thanks. I found this solution as well. My goal here is to understand why the proposed solution doesn't work (for my learning). I'm also hoping the other solution is faster as this solution runs slow for me. – Ben Mar 11 '18 at 17:10
  • both the queries don't work because are simply left join .. in this way you just join the the rows based on (all) startDT ..or the startDT < startDT .non evaluation about the max predCustID are involved .. in this way you never find the max value for each startDT ... In SQL for this type of eavl there specificaaggregation function and operator sa group by .. hope this is usefull. – ScaisEdge Mar 11 '18 at 17:16
  • and for improve performance check for proper index on startDT – ScaisEdge Mar 11 '18 at 17:18
  • @Ben (nice handle btw) on a properly indexed table, no solution will be faster than this – Strawberry Mar 11 '18 at 17:42
  • This is the solution I'm rolling with for now. Thanks a ton to both of you. – Ben Mar 13 '18 at 05:04