Questions tagged [natural-sort]
Natural sorting is ordering values the way a human would. For example, "a2" comes before "a10" while a simple minded character-by-character comparison would put "a10" before "a2". Especially useful for IP addresses or version numbers.
10
questions
14
votes
3
answers
6k
views
How to treat numbers within strings as numbers when sorting ("A3" sorts before "A10", not after)
For all of these queries:
SELECT label FROM personal.storage_disks ORDER BY label ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "C" ASC;
SELECT label FROM personal....
- 527
0
votes
3
answers
1k
views
Sort varchar by its numeric fields
I'm sure this has been asked before, but since the question can be formulated in many different ways it's difficult to find the proper answer.
I have an orders table with a varchar field for the order ...
0
votes
1
answer
3k
views
postgresql - trouble to order a list obtained by string_agg
I obtained a list of numbers (and text) by a string_agg.
For instance, that :
"09,12,20,200,217,400,500,90,91,92,Exp3"
This is the string_agg:
string_agg(DISTINCT t_ligne.ligne_code::text, ','::...
- 1
2
votes
3
answers
8k
views
Sorting by month by chronological occurrence when the year doesn't matter?
Given a table with a date column, and given a query that selects dates in a given range of the year (like "November 20th through February 20th"), and where the year portion of the date doesn't matter (...
- 291
10
votes
1
answer
9k
views
ORDER BY and comparison of mixed strings of letters and numbers
We need to do some reporting on values that are usually mixed strings of numbers and letters that need to be sorted 'naturally'. Things like, e.g. "P7B18" or "P12B3". @The strings will mostly be ...
- 9,568
5
votes
2
answers
3k
views
Oracle sort order changed
We recently deployed a new environment with a newer version of Oracle (12c) instead of 11r2.
Only recently I noticed my data returned is sorted differently.
Instead of having small letters, capital ...
- 265
9
votes
2
answers
7k
views
Why does Postgres ORDER BY seem to halfway ignore leading underscores?
I have an animal table with a name varchar(255), and I've added rows with the following values:
Piranha
__Starts With 2
Rhino
Starts With 1
0_Zebra
_Starts With 1
Antelope
_Starts With 1
When I run ...
- 1,119
26
votes
3
answers
16k
views
How to ORDER BY typical software release versions like X.Y.Z?
Given a "SoftwareReleases" table:
| id | version |
| 1 | 0.9 |
| 2 | 1.0 |
| 3 | 0.9.1 |
| 4 | 1.1 |
| 5 | 0.9.9 |
| 6 | 0.9.10 |
How do I produce this output?
| id | version ...
- 447
7
votes
1
answer
23k
views
Order by alphabet and then by numbers
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 ...
- 145
15
votes
4
answers
4k
views
Is there a collation to sort the following strings in the following order 1,2,3,6,10,10A,10B,11?
I have a database with a VARCHAR column that contains integers of varying length. I want to sort them so 10 comes after 9, not 1, and 70A comes after 70. I was able do this with PATINDEX(), a CTE, and ...
- 2,667