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.

Filter by
Sorted by
Tagged with
0
votes
1answer
166 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, ','::...
0
votes
3answers
4k 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 (...
9
votes
1answer
5k 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 ...
5
votes
2answers
2k 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 ...
6
votes
2answers
1k 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 ...
10
votes
3answers
6k 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 ...
4
votes
1answer
14k 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 ...
12
votes
4answers
2k 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 ...