Alex Rivera | Logout

Oracle - Return shortest string value in a set of rows

Asked 2010-04-20T16:14:49.240
9

I'm trying to write a query that returns the shortest string value in the column. For ex: if ColumnA has values ABCDE, ZXDR, ERC, the query should return "ERC". I've written the following query, but I'm wondering if there is any better way to do this?

The query should return a single value.

select distinct ColumnA from
(
  select ColumnA, rank() over (order by length(ColumnA), ColumnA) len_rank 
    from TableA where ColumnB = 'XXX'
)
where len_rank <= 1
sql oracle-database plsql
Edit
Report

1 Answer

2

This would help you get all the strings with the minimum length in the column.

select ColumnA 
from TableA 
where length(ColumnA) = (select min(length(ColumnA)) from TableA)

Hope this helps.

answered 2010-05-31T07:19:18.773

Your Answer