Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have records like

 A5
 A4
 Z1
 B2
 C7
 C1A
 C11A
 B1
 B4

I want them to be sorted in this manner

A4
A5
B1
B2
B4
C1
C11A
C7
Z1

using the ORDER BY clause.

I want them to be sorted by alphabets and then by numeric values.

share|improve this question
2  
Take a look here stackoverflow.com/questions/11417975/… –  Johnny000 Oct 31 '13 at 9:06
    
by alphabets and then by numeric values .. so what about the trailing A? Ignore it? Please be more specific. –  Erwin Brandstetter Oct 31 '13 at 23:36

1 Answer 1

For your request:

sorted by alphabets and then by numeric values

  • I am assuming (deriving from your sample data), that only the first letter should be treated as text to sort by (easy to adapt).

  • Further assuming that you want to sort by the first number in the string next (digits only).

  • To break ties with trailing characters I finally order by the column as a whole. May or may not be needed.

SELECT *
FROM   tbl
ORDER  BY left(col, 1)  -- 1st letter as text
      ,substring(fest, '\d+')::int NULLS FIRST  -- first number in string as int
      ,col  -- whole columns as cheap tiebreaker

-> SQLfiddle demo.

As @Johnny000 commented, you can find more in this related answer on SO.
For even more complex requirements, consider this related question on SO.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.