snippetsqlModerate
Order by alphabet and then by numbers
Viewed 0 times
alphabetordernumbersthenand
Problem
I have records like
I want them to be sorted in this manner
using the
I want them to be sorted by alphabets and then by numeric values.
A5
A4
Z1
B2
C7
C1A
C11A
B1
B4I want them to be sorted in this manner
A4
A5
B1
B2
B4
C1
C11A
C7
Z1using the
ORDER BY clause.I want them to be sorted by alphabets and then by numeric values.
Solution
For your request:
sorted by alphabets and then by numeric values
I assume (deriving from your sample data) you want to
-
The first letter, treated as
-
The first number (consecutive digits), treated as
-
The whole string to break remaining ties, treated as
SQL Fiddle.
More details:
sorted by alphabets and then by numeric values
I assume (deriving from your sample data) you want to
ORDER BY:-
The first letter, treated as
text.-
The first number (consecutive digits), treated as
integer.-
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 tiebreakerSQL Fiddle.
More details:
- Alphanumeric sorting with PostgreSQL
- Humanized or natural number sorting of mixed word-and-number strings
Code Snippets
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 tiebreakerContext
StackExchange Database Administrators Q#52569, answer score: 10
Revisions (0)
No revisions yet.