Sorting in SQl query

Question

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?

what do you mean by rownum = 0? don't you mean rownum = 1? — Jeffrey Kemp
– Jeffrey Kemp, Commented Jun 26, 2012 at 7:17

A.B.Cade · Accepted Answer · 2012-06-25 10:30:16Z

3

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

edited Jun 25, 2012 at 10:30

answered Jun 25, 2012 at 10:22

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

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

2 Comments

Anand Over a year ago

but by doing so, I will not be able to sort single/double character values. For eg. 1) 'F' and 'H' 2) 'DF' and 'HG'

Anand Over a year ago

edited my explanation little bit to make it more understandable

Nahuel Fouilleul · Accepted Answer · 2012-06-25 10:41:55Z

0

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

answered Jun 25, 2012 at 10:41

Nahuel Fouilleul

19.5k2 gold badges36 silver badges37 bronze badges

Collectives™ on Stack Overflow

Sorting in SQl query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related