CODEWITHSUNDEEP
sqloracle-database
Anand
Anand

Reputation: 21320

Sorting in SQl query

I have a column in my table that stores max 2 characters. For eg. B,AK,SE etc.

Suppose I have two rows in that table, one having 'K' and other having 'BA' as a value.

Requirement in my project is such that I have to get the maximum value from the table based on the no. of characters in case there is a difference in the length. In this case, maximum value will be 'BA' not 'K'.

But when I use 'order by column desc' with rownum = 1 in my query, 'K' is returned as rownum 0th value.

If I have values 'D','BA','AK','C' then output should be 'BA','AK','C','D' Just want to know if there is any way I can fulfill my requirement?

Upvotes: 0

Views: 76

Answers (2)

Nahuel Fouilleul
Nahuel Fouilleul

Reputation: 19315

try :

select * from (
  select row_number() over(order by length(col_name) desc, col_name desc) rn, col_name
  from tab_name
) where rn = 1

Upvotes: 0

A.B.Cade
A.B.Cade

Reputation: 16905

Simplest way could be:

ORDER BY length(<col_name>) desc

UPDATE - if you need to order by the value also, you can do:

ORDER BY length(<col_name>) desc, <col_name> desc

Upvotes: 3

Related Questions