Database：SAP HANA
First table is original data.
Original
| MATNR | EAU | QTY |
|---|---|---|
| 1 | 30 | 1 |
| 1 | 30 | 50 |
| 2 | 70 | 1 |
| 2 | 70 | 50 |
| 2 | 70 | 100 |
| 3 | 10 | 1 |
Second table is output after group by.
Output ( Group by MATNR )
| MATNR | EAU | QTY |
|---|---|---|
| 1 | 30 | 1, 50 |
| 2 | 70 | 1, 50, 100 |
| 3 | 10 | 1 |
Third table is the expected result.
Output ( Where EAU > second QTY )
| MATNR | EAU | QTY |
|---|---|---|
| 2 | 70 | 1, 50, 100 |
Is there a sql like WHERE SPLIT(QTY, ', ')[1] > EAU ?
You don't specify what database you are using, but one method would use row_number():
select matnr, eau,
listagg(qty, ', ') within group (order by qty)
from (select t.*,
row_number() over (partition by matnr order by qty) as seqnum
from t
) t
group by matnr, eau
having eau > sum(case when seqnum = 2 then qty end) ;
Note: This uses generic SQL. The exact syntax (particularly for the string aggregation) depends on the database you are using.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments