CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Lucy82
Lucy82

Reputation: 693

Oracle SQL - select all maximum column values from query

let's say this is my table:

 Name    |  Surname      | Serial no.  | Price
-----------------------------------------------
 John    |  Smith        | A12444dW33  | 1,234 
 Paul    |  Harrison     | D2344fGGfd  | 2,5
 Richard |  Morris       | We945kfGGE  | 3
 George  |  Washington   | ssf4gt66hJ  | 7,39494

How do I get actual LONGEST values for each column in ONE row, based on length ?

I need something that will be valid for every datatype - NUMBER, VARCHAR,DATE etc.

Output for example table should be (Serial no. are all same length, so that is not important here...):

 Name    |  Surname      | Serial no.  | Price
-----------------------------------------------
 Richard |  Washington   | A12444dW33  | 7,39494

All questions I found are focused on use with one column only, but I need solution for all columns.

Upvotes: 1

Views: 75

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use keep:

select max(name) keep (dense_rank first order by length(name) desc),
       max(Surname) keep (dense_rank first order by length(Surname) desc),
       max(serial) keep (dense_rank first order by length(serial) desc),
       max(price) keep (dense_rank first order by length(price) desc)
from t;

Upvotes: 3

MT0
MT0

Reputation: 167962

You can use MAX( column ) KEEP ( DENSE_RANK [FIRST|LAST] ORDER BY ... ):

SELECT MAX( Name      ) KEEP ( DENSE_RANK FIRST ORDER BY LENGTH( Name      ) DESC ) AS Name,
       MAX( Surname   ) KEEP ( DENSE_RANK FIRST ORDER BY LENGTH( Surname   ) DESC ) AS Surname,
       MAX( Serial_no ) KEEP ( DENSE_RANK FIRST ORDER BY LENGTH( Serial_no ) DESC ) AS serial_no,
       MAX( Price     ) KEEP ( DENSE_RANK FIRST ORDER BY LENGTH( Price     ) DESC ) AS Price
FROM   your_table;

outputs:

NAME    | SURNAME    | SERIAL_NO  |   PRICE
:------ | :--------- | :--------- | ------:
Richard | Washington | ssf4gt66hJ | 7.39494

db<>fiddle here

Upvotes: 3

Related Questions