Reputation: 6629
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
?
Upvotes: 1
Views: 497
Reputation: 72511
There are a number of ways to do this.
ARRAY_AGG
in the HAVING
clause to pull outSELECT
t.MATNR,
t.EAU,
STRING_AGG(t.QTY, ',')
FROM YourTable t
GROUP BY
t.MATNR,
t.EAU
HAVING t.EAU > ARRAY_AGG(t.QTY ORDER BY t.QTY)[2];
SELECT
t.MATNR,
t.EAU,
STRING_AGG(t.QTY, ',')
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY t.MATNR, t.EAU ORDER BY t.QTY) AS rn
FROM YourTable t
) t
GROUP BY
t.MATNR,
t.EAU
HAVING t.EAU > SUM(CASE WHEN t.rn = 2 THEN t.QTY END);
Upvotes: 1
Reputation: 1271241
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.
Upvotes: 1