7

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.

Improve this question
1

1 Answer 1

Reset to default
10

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:

Improve this answer
2
  • Is substring(text,text) an undocumented variant of substring(string from pattern)?
    – Jack Douglas
    Mar 5, 2015 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, 2015 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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