0

I've a requirement where I need to fetch users latest 5 remarks data including his User ID.
So, I've used cross apply and used RemarksData as alias name.

SQL Query:

select DD.DEBTOR_ID,RemarksData.*
from DMS_DEBTOR DD 
cross APPLY 
(
SELECT TOP 5 DF.REMARK as [Remarks]
FROM  DMS_FOLLOWUP DF
WHERE  DF.DEBTOR_ID = DD.DEBTOR_ID
ORDER  BY DF.FOLLOWUP_TIME desc
) RemarksData where DD.BATCHNO in ('MBB EX-24') and DD.flagabort='0' order by DD.NAME

My Query Sample Data:

ID    NAME      Remarks
-----------------------------------------
2881173   ARIFFIN   Sent To FV
2881173   ARIFFIN   CHECKING PAYMENT
2881173   ARIFFIN   FULLSETTLEMENT
2881173   ARIFFIN   CALLED BV
2881173   ARIFFIN   BROKEN PROMISE
-----------------------------------------
1682126   ASRI      Waiting For Results
1682126   ASRI      CHECKING PAYMENT
1682126   ASRI      PROPOSAL
1682126   ASRI      CALLED
1682126   ASRI      BROKEN PROMISE
-------------------------------------------
1703446   ASRUL     Sent To FV
1703446   ASRUL     CHECKING PAYMENT
1703446   ASRUL     PROPOSAL
1703446   ASRUL     RNR
1703446   ASRUL     BROKEN PROMISE

For clear understanding, I've separated each user records.

Here as you can see, that query gives out every user with his last 5 remarks.

Now, I'm planning to convert the same query to MySQL but unable to find the alternative of cross Apply.

Can anyone suggest me, if this SQL query can be done in any other way or at least convert it to MySQL.

I've tried some online converters also but nothing worked for me.

I can provide additional information if needed.

MySQL Query which I've tried is :

SELECT
   DD.DEBTOR_ID,DD.CARDNO,DD.ACCOUNTNO,DD.NAME,
  ,(SELECT DF.REMARK as Remarks
      FROM DMS_FOLLOWUP DF
     WHERE DF.DEBTOR_ID = DD.DEBTOR_ID
  ORDER BY DF.FOLLOWUP_TIME DESC
     LIMIT 1
   )   AS VALUE
FROM
  DMS_DEBTOR DD limit 10;

But it is returning only 1 record for every debtor(user) here. But I need the latest 5 records. If I put the LIMIT 5, then I'm getting an issue.

RealSteel
  • 1,871
  • 3
  • 37
  • 74
  • Stack Overflow is *not* a code translation service. You are expected to try to **write the code yourself** and then if you have a problem you can **post what you've tried** with a **clear explanation of what isn't working** and providing a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). I suggest reading [how to ask a good question](http://stackoverflow.com/help/how-to-ask). – John Conde May 16 '17 at 14:18
  • You've been around long enough that you're expected to *try* something before posting here. – John Conde May 16 '17 at 14:19
  • 1
    Cross apply equivalent would be a window function on the user using a row_number. No row number in mySQL. So mySQL equivalent would be two user variables; resetting on each ID the rowNumber. and then the query becomes a trivial join with a row number <=5. – xQbert May 16 '17 at 14:21
  • Possible duplicate of [CROSS/OUTER APPLY in MySQL](http://stackoverflow.com/questions/36869221/cross-outer-apply-in-mysql) – stuartd May 16 '17 at 14:32
  • @JohnConde : Thanks for the suggestion. – RealSteel May 17 '17 at 12:32

1 Answers1

3

You don't have cross apply in MySQL. Nor do you have row_number() (which would also be a natural way to express this. Instead, you can use variables:

select DD.DEBTOR_ID,RemarksData.*
from DMS_DEBTOR DD join
     (select df.debtor_id, df.remark as Remarks,
             (@rn := if(@d = df.debtor_id, @rn + 1,
                        if(@d := df.debtor_id, 1, 1)
                       )
             ) as rn
      from DMS_FOLLOWUP DF cross join
           (select @rn := 0, @d := -1) params
      order by df.debtor_id, df.followup_time desc
     ) df
     on  DF.DEBTOR_ID = DD.DEBTOR_ID
where DD.BATCHNO in ('MBB EX-24') and DD.flagabort = '0' and
      rn <= 5
order by DD.NAME, rn;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select df.debtor_id, df.remark as Remarks, (@rn := if(@d = df.deb' at line 3 – RealSteel May 17 '17 at 05:45
  • I'm getting this error. Can you please help me on this? – RealSteel May 17 '17 at 05:45