Oracle - Return shortest string value in a set of rows

I'm trying to write a query that returns the shortest string value in the column. For ex: if Column开发者_如何学PythonA 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

How about:

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

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.

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.

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
/

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.

I know this has very long answer and very old. But I think I know another good way.

Because all answer is using sub query, it was not suitable for my situation. So I found a way which was not used sub query.

Say I have a data as following.

select  *
from    (select 'x' f  from dual union all select 'aaaaa' from dual) a

--Output

x
aaaaa

If I select min of value it returns 'aaaaa' because 'a' is smaller than 'x' in ascii order.

select  min(a.f)
from    (select 'x' f  from dual union all select 'aaaaa' from dual) a

--Output

aaaaa

But if I select min of length it returns 1(which is for 'x' value) because 1 is smaller than 5 in numeric order.

select  min(length(a.f))
from    (select 'x' f  from dual union all select 'aaaaa' from dual) a

--Output

1

And if I select min of length converted to padded value also returns '0000000001'(which is for 'x' value) because '0000000001' is smaller than '0000000005' in ascii order.

select  min(lpad(length(a.f), 10, '0'))
from    (select 'x' f  from dual union all select 'aaaaa' from dual) a

--Output

0000000001

And I can bind it with value itself.

select  lpad(length(a.f), 10, '0') || a.f
from    (select 'x' f  from dual union all select 'aaaaa' from dual) a

--Output

0000000001x
0000000005aaaaa

Now I can select min of length and value together.

select  min(lpad(length(a.f), 10, '0') || a.f)
from    (select 'x' f  from dual union all select 'aaaaa' from dual) a

--Output

0000000001x

Now I can get only value by using substr.

select  substr(min(lpad(length(a.f), 10, '0') || a.f), 11, 999)
from    (select 'x' f  from dual union all select 'aaaaa' from dual) a

--Output

x

 select city,length(city) from (select city from station ORDER BY length(city) 
 ASC, CITY ASC)where rownum=1;


 select city,length(city) from (select city from station ORDER BY length(city) 
 DESC, CITY ASC)where rownum=1;