獲取 DB2 列中最長的字元串

我試圖在給定的 DB2 表中獲取最長的字元串。我知道架構中列的最大長度，但我想生成一個顯示最長實際值的報告。

例如，如果表格看起來像

ID    NAME    EMAIL         SALARY
1234  Julio   j@myco.com    12345.00
1235  Sara    s@myco.net    23456.00
1236  Sid     sid@myco.net  452.00

我怎樣才能得到值

NAME    EMAIL        SALARY
Julio   sid@myco.net 23456.00

請注意，我不需要最長值的實際長度，我有興趣查看這些值本身。

嘗試

SELECT *
FROM (
   SELECT *
   ,   MAX(LENGTH(EMAIL)) OVER() AS MAX_LEN
   FROM
       EMAIL_TABLE
)
WHERE LENGTH(EMAIL) = MAX_LENGTH

對我來說，你似乎想在每一列中獲得最長的結果（也許薪水最高）。試試這個：

SELECT
   ( SELECT name FROM x ORDER BY LENGTH(name) DESC FETCH FIRST 1 ROW ONLY ) AS name,
   ( SELECT email FROM x ORDER BY LENGTH(email) DESC FETCH FIRST 1 ROW ONLY ) AS email,
   ( SELECT salary FROM x ORDER BY salary DESC FETCH FIRST 1 ROW ONLY ) AS salary
 FROM SYSIBM.SYSDUMMY1

另一種選擇：

WITH ord_tbl AS
(
  SELECT name, email, salary,
        ROWNUMBER()OVER(ORDER BY LENGTH(name) DESC) AS name_ord,
        ROWNUMBER()OVER(ORDER BY LENGTH(email) DESC) AS email_ord,
        ROWNUMBER()OVER(ORDER BY salary DESC) AS salary_ord
    FROM x
)
SELECT MIN(DECODE(name_ord, 1, name)) AS name,
     MIN(DECODE(email_ord, 1, email)) AS email,
     MIN(DECODE(salary_ord, 1, salary)) AS salary
 FROM ord_tbl

引用自：https://dba.stackexchange.com/questions/241501