CODEWITHSUNDEEP
sqloracle-database
Dung Pham
Dung Pham

Reputation: 39

Oracle SQL MIN(LENGTH(text)) OVER PARTITION

I'm trying query data from a table like

BUKRS KUNNR SORTL
0001 2536 AB
0002 2536 ABCDE
0003 2536 ABC
0004 2536 Null
0005 2536 ' '
0001 4515 GHU
0002 4515 GHUKL
0003 4515 GH
0004 4515 Null
0005 4515 ' '

I want to query to result liKe

BUKRS KUNNR SORTL SORTL_FINAL
0001 2536 AB AB
0002 2536 ABCDE AB
0003 2536 ABC AB
0004 2536 Null AB
0005 2536 ' ' AB
0001 4515 GHU GH
0002 4515 GHUKL GH
0003 4515 GH GH
0004 4515 Null GH
0005 4515 ' ' GH

Data in column SORTL contain Null, ' ' (space). I want to get data in SORTL to SORTL_FINAL with the smallest length except Null and ' ' (space) over KUNNR. Thanks

Upvotes: 0

Views: 970

Answers (3)

SillyInformatics
SillyInformatics

Reputation: 1

A variant without using a CTE which I find easier to read as an SQL beginner. The code is derivated from the answer of Tim Biegeleisen. Tested in Postgres but should work in Oracle as well:

SELECT BUKRS, KUNNR, SORTL,
          MIN(NULLIF(SORTL, ' ')) OVER (PARTITION BY KUNNR) AS SORTL_FINAL
FROM yourtable
ORDER BY KUNNR, BUKRS;

I used NULLIF to sort the ' ' strings to the end so MIN always finds the shortest string.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270623

You can use first_value():

select t.*,
       first_value(sortl) over (partition by kunnr
                                order by length(sortl) nulls last, sortl
                               ) as shortest_sortl
from t;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522292

We can try using ROW_NUMBER here:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY KUNNR
                                   ORDER BY LENGTH(SORTL), SORTL) rn
    FROM yourTable t
)

SELECT BUKRS, KUNNR, SORTL,
       MAX(CASE WHEN rn = 1 THEN SORTL END) OVER (PARTITION BY KUNNR) AS SORTL_FINAL
FROM cte
ORDER BY KUNNR, BUKRS;

The logic here is to assign a row number to each KUNNR group using the length of the SORTL value. In cases where there be more than one SORTL with the same length, we sort alphebetically. Then, we use conditional aggregstion over each KUNNR group to find the "shortest" value.

Upvotes: 0

Related Questions