带顶记录的group by子句-mysql

SELECT TOP 1 W.LineItems_LineItemID, P.CurrentRate, P.CurrentEffectiveDate 
  FROM WorkOrderLineItems W 
  JOIN PayScaleLoaclJObCodes P ON P.JobCodeID = W.LineItems_LineItemID 
  WHERE P.PayScalesLocal_ID = 29 AND W.WorkOrderCurrent_WorkOrderID = 120420
  AND P.CurrentEffectiveDate <= '2018-05-27' 
  ORDER BY P.CurrentEffectiveDate DESC

select top子句用于指定要返回的记录数。对于你的案子,你只需要 TOP 1 就一个。

或

SELECT W.LineItems_LineItemID, P.CurrentRate, P.CurrentEffectiveDate 
  FROM WorkOrderLineItems W 
  JOIN PayScaleLoaclJObCodes P ON P.JobCodeID = W.LineItems_LineItemID 
  WHERE P.PayScalesLocal_ID = 29 AND W.WorkOrderCurrent_WorkOrderID = 120420
  AND P.CurrentEffectiveDate <= '2018-05-27' 
  ORDER BY P.CurrentEffectiveDate DESC 
  LIMIT 1