Oracle SQL - select all maximum column values from query

Question

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.

DATE values all have the same length...

user330315
– user330315

2020-01-16 12:05:13 +00:00
Commented Jan 16, 2020 at 12:05 — user330315
– user330315, Commented Jan 16, 2020 at 12:05

Gordon Linoff · Accepted Answer · 2020-01-16 12:07:38Z

3

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;

edited Jan 16, 2020 at 12:07

answered Jan 16, 2020 at 12:04

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lucy82 Over a year ago

,thanks, I will try ASAP. I'm only afraid of performance issues, my actual queries are slow to process, this will probably make them even slower.

Gordon Linoff Over a year ago

@Lucy82 . . . I have found that keep is more efficient than other alternatives when I've kept track of the performance.

Lucy82 Over a year ago

amazing, this thing is super fast. But what about possible white spaces in VARCHAR, should I use TRIM over values just in case ?

Gordon Linoff Over a year ago

@Lucy82 . . . If you want to remove spaces, then use trim().

MT0 · Accepted Answer · 2020-01-16 12:05:19Z

3

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

answered Jan 16, 2020 at 12:05

MT0

173k12 gold badges70 silver badges136 bronze badges

Collectives™ on Stack Overflow

Oracle SQL - select all maximum column values from query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related