CODEWITHSUNDEEP
sqlhana
YuTing
YuTing

Reputation: 6629

Is there a where condition to compare the value of the string after splitting?

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

Answers (2)

Charlieface
Charlieface

Reputation: 72511

There are a number of ways to do this.

  • In a DBMS that supports arrays, you can use ARRAY_AGG in the HAVING clause to pull out
SELECT
  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];
  • You could use a row-numbering solution
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

Gordon Linoff
Gordon Linoff

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

Related Questions