Oracle - Return shortest string value in a set of rows

Question

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

Answer 1

There are two parts to this question. An alternative way of determining the shortest string is the old-fashioned sub-query:

select distinct ColumnA 
from tablea 
where length(ColumnA) = ( select min(length(ColumnA)) 
                          from TableA 
                          where ColumnB = 'XXX'
                        )
/

Which is better? It depends on the indexing, data volumes, etc but I would guess your version is likely to perform better. It might also give slightly different results, unless you duplicated the where ColumnB = 'XXX' in the outer query.

Like your solution this query will return one row for each value of ColumnA which is three characters long. If you want to return a single row you can do so by restricting it with rownum. It you want to apply some criterion to determine which is the first row you need to embed it in a further outer query (using my query but a variant on yours would work too) ...

select * from (
    select ColumnA 
    from tablea 
    where length(ColumnA) = ( select min(length(ColumnA)) 
                              from TableA 
                              where ColumnB = 'XXX'
                            )
    order by ColumnA
    ) 
where rownum = 1
/

Answer 2

How about:

select ColumnA
from
(
  select ColumnA
  from tablea
  order by length(ColumnA) ASC
)
where rownum = 1

Answer 3

Expanding on APC's answer a little bit, I think this will be slightly better:

SELECT DISTINCT columna
  FROM tablea t1
 WHERE EXISTS ( SELECT 1 FROM tablea t2
                 WHERE LENGTH(t2.columna) = MIN(LENGTH(t1.columna)) )
   AND rownum = 1

IIRC, APC's subselect will be run once for each row in tablea. This, I believe, does not.

Mind you, if you have multiple rows with the same length string in columna, you may not get consistent results from this query run multiple times.

Answer 4

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.

Answer 5

The simplest way, with a single table access and without subqueries:

SQL> create table mytable (txt)
  2  as
  3  select 'ABCDE' from dual union all
  4  select 'ZXDR' from dual union all
  5  select 'ERC' from dual
  6  /

Table created.

SQL> set autotrace on explain
SQL> select min(txt) keep (dense_rank first order by length(txt)) txt
  2    from mytable
  3  /

TXT
-----
ERC

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'MYTABLE'

EDIT: I adjusted the example to fit your example query even more:

SQL> create table tablea (columna,columnb)
  2  as
  3  select 'ABCDE', 'XXX' from dual union all
  4  select 'ZXDR', 'XXX' from dual union all
  5  select 'ERC', 'XXX' from dual
  6  /

Table created.

SQL> set autotrace on explain
SQL> select min(columna) keep (dense_rank first order by length(columna)) columna
  2    from tablea
  3   where columnb = 'XXX'
  4  /

COLUM
-----
ERC

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TABLEA'

Regards, Rob.