Can you help me with this oracle sql query-CodePudding

Return the employees with the least and most characters in their name (full name). If there are many, select only those that come first in alphabetical order. Only two employees will therefore be returned. You will project their names. (full name), the size of their name, and the ASCII value of their name.

here is what i did :

SELECT FIRST_NAME ||' '|| LAST_NAME FULL_NAME ,
       (LENGTH(FIRST_NAME ||' '|| LAST_NAME)-1) Taille_caractere , 
       (ascii(FIRST_NAME ||' '|| LAST_NAME)-1)  Valeur_ASCII
  FROM EMPLOYEES;

but now i want to display two employees , the one with less character and the one with more character

CodePudding user response：

You can row_number() the rows twice and take the terminal cases

select Taille_caractere, Valeur_ASCII
from (
    SELECT FIRST_NAME ||' '|| LAST_NAME FULL_NAME ,
           (LENGTH(FIRST_NAME ||' '|| LAST_NAME)-1) Taille_caractere , 
           (ascii(FIRST_NAME ||' '|| LAST_NAME)-1)  Valeur_ASCII,
      row_number() over(order by LENGTH(FIRST_NAME ||' '|| LAST_NAME), FIRST_NAME ||' '|| LAST_NAME) rn1,
      row_number() over(order by LENGTH(FIRST_NAME ||' '|| LAST_NAME) DESC, FIRST_NAME ||' '|| LAST_NAME) rn2,
      FROM EMPLOYEES
) t
where rn1 = 1 or rn2 = 1;

CodePudding user response：

Here you go:

WITH cteEmployees
  AS (SELECT FIRST_NAME,
             LAST_NAME,
             FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME,
             LENGTH(FIRST_NAME || ' ' || LAST_NAME) AS FULL_NAME_LENGTH
        FROM EMPLOYEES),
  cteLeast AS
     (SELECT *
        FROM (SELECT *
                FROM cteEmployees
                ORDER BY FULL_NAME_LENGTH ASC,
                         LAST_NAME ASC,
                         FIRST_NAME ASC)
        WHERE ROWNUM = 1),
  cteMost AS
     (SELECT *
        FROM (SELECT *
                FROM cteEmployees
                ORDER BY FULL_NAME_LENGTH DESC,
                         LAST_NAME ASC,
                         FIRST_NAME ASC)
        WHERE ROWNUM = 1)
SELECT *
  FROM cteLeast
UNION ALL
SELECT *
  FROM cteMost

The problem of determining the ASCII value of the name I leave to you since I don't understand what that means.