Limit rows by first two dates within column. How?

Question

Is it possible to query thats only first and second date of the customer? I tried doing the UP TO 2 ROWS but it only limits the table only to 2 rows.

SELECT knvv~kunnr vbak~vbeln vbak~erdat FROM vbak INNER JOIN knvv ON vbak~kunnr = knvv~kunnr.

The sample result of the above query would be:

Customer no.    Document No            Date
1               100000                 01/01/18
1               200000                 01/02/18
1               300000                 01/03/18
1               400000                 01/04/18
2               100001                 01/01/18
2               200000                 01/04/18
2               100040                 01/06/18

But what i need that it only limits the first two dates per customer. The result must be like this. It should only get like the first two dates of each customer just like the result below. Is it possible to do it in the query?

Customer no.    Document No            Date
1               100000                 01/01/18
1               200000                 01/02/18
2               100001                 01/01/18
2               200000                 01/04/18

some answers there: https://answers.sap.com/questions/607528/is-it-possible-to-query-of-only-first-and-second-d.html — Sandra Rossi, Aug 21 '18 at 13:35

score 1 · Answer 1 · answered Aug 21 '18 at 03:59

SELECT CustomerNo,DocumentNo,Date,(@Count:= if(@TempID - CustomerNo = 0,@Count +1,1)) Counter,(@TempID:=CustomerNo) Tempid
FROM vbak, (Select @Count:=0) counter, (Select @TempID:=0) tempid
having Counter<= 2 order by CustomerNo;

you can try this. Basically I declared 2 variables (@Count and @TempID) and both set as 0. Initially for the first row, @TempID - CustomerNo = -1 makes the condition false and sets it to 1 rather then increment it. Then, @TempID is set to the current CustomerNo of that row.

The next row would produce @TempID - CustomerNo = 0 and causes the condition to be true and increment @Count + 1.

So on and so forth,

The Having Statement selects Counter that is less or equal to 2 which then returns the desired results.

hopefully this would give you an idea for your application.

Works in native SQL, but by the looks of it he's looking for a query in SAP's OpenSQL variant. (Note the `~` accessors in field names.) — Florian, Aug 22 '18 at 15:22
Yes, i only realize it after awhile. Before the question was edited and removed tag 'mysql', i immediately assumed it was for mysql. Nevertheless, hopefully this could be used as an idea for @gwapo in his application. — Ryan Tan, Aug 23 '18 at 01:51

score 1 · Answer 2 · answered Aug 24 '18 at 08:27

I couldn't find a way to do this with a single query in OpenSQL. It just doesn't seem to offer the kind of sub-query or window function that would be required.

However, I noticed that you added the hana tag. With SAP HANA, this can be quite easily realized with an ABAP-Managed Database Procedure (AMDP) or an equivalent scripted Calculation View:

METHOD select BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
  USING vbak.

  lt_first_dates = SELECT kunnr,
                          min(erdat) AS erdat
                     FROM vbak
                     GROUP BY kunnr;

  lt_second_dates = SELECT kunnr,
                           min(erdat) AS erdat
                      FROM vbak
                      WHERE (kunnr, erdat) NOT IN ( SELECT * FROM :lt_first_dates )
                      GROUP BY kunnr;

  lt_first_two_dates = SELECT * FROM :lt_first_dates
                       UNION
                       SELECT * FROM :lt_second_dates;

  et_result = SELECT src.kunnr,
                     src.vbeln,
                     src.erdat
                FROM vbak AS src
                WHERE (kunnr, erdat) IN ( SELECT * FROM :lt_first_two_dates )
                ORDER BY kunnr, vbeln, erdat;

ENDMETHOD.

Limit rows by first two dates within column. How?

2 Answers2