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

1 Answer 1

up vote 5 down vote accepted

For your request:

sorted by alphabets and then by numeric values

I assume (deriving from your sample data) you want to ORDER BY:

  1. The first letter, treated as text.

  2. The first number (consecutive digits), treated as integer.

  3. The whole string to break remaining ties, treated as text. 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

SQL Fiddle.

More details:

share|improve this answer
    
Is substring(text,text) an undocumented variant of substring(string from pattern)? –  Jack Douglas Mar 5 at 14:08
1  
@JackDouglas: Yes. Both variants actually result in the same function call internally. Check the output of: EXPLAIN VERBOSE SELECT substring(col FROM 'FROM') AS s1, substring(col, 'COMMA') AS s2 FROM (SELECT 'foo'::text) t(col); –  Erwin Brandstetter Mar 5 at 20:50

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.