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 sequences of letters then numbers alternating. The number of these segments and the length of each could vary, though. We'd like the numeric portions of these to be sorted in numeric order. Obviously, if I just handle those string values directly with ORDER BY, then "P12B3" is going to come before "P7B18", since "P1" is earlier than "P7", but I'd like the reverse, as "P7" naturally precedes "P12". I'd also like to be able to do range comparisons, e.g. @bin < 'P13S6' or some such. I don't have to handle floating point or negative numbers; these will strictly be non-negative integers that we're dealing with. String lengths and number of segments could potentially be arbitrary, with no fixed upper bounds. In our case, string casing isn't important, though if there's a way to do this in a collation-aware fashion, others might find that useful. The ugliest part of all this is I'd like to be able to do both ordering, and range filtering in the WHERE clause. If I were doing this in C#, it would be a pretty simple task: do some parsing to separate the alpha from the numeric, implement IComparable, and you're basically done. SQL Server, of course, doesn't appear to offer any similar functionality, at least as far as I'm aware. Anybody know any good tricks to make this work? Is there some little-publicized ability to create custom CLR types that implement IComparable and have this behave as expected? I'm also not opposed to Stupid XML Tricks (see also: list concatenation), and I've got CLR regex matching/extracting/replacement wrapper functions available on the server as well. **EDIT:** As a slightly more detailed example, I'd want the data to behave something like this. SELECT bin FROM bins ORDER BY bin bin -------------------- M7R16L P8RF6JJ P16B5 PR7S19 PR7S19L S2F3 S12F0 i.e. break the strings into tokens of all letters or all numbers, and sort them either alphabetically or numerically respectively, with the leftmost tokens being the most significant sorting term. Like I mentioned, piece of cake in .NET if you implement IComparable, but I don't know how (or if) you can do that sort of thing in SQL Server. It's certainly not something I've ever come across in 10 or so years of working with it.

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()](http://msdn.microsoft.com/en-us/library/ms188395.aspx) , a CTE, and CASE statements in the WHERE clause. However, I was wondering if there was a collation where this would be unecessary.

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 | | 1 | 0.9 | | 3 | 0.9.1 | | 5 | 0.9.9 | | 6 | 0.9.10 | | 2 | 1.0 | | 4 | 1.1 |

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.storage_disks ORDER BY label COLLATE "POSIX" ASC; SELECT label FROM personal.storage_disks ORDER BY label COLLATE "default" ASC; The output is always: DISK 1, DISK 10, DISK 2, DISK 3, [...] But, I want and expect: DISK 1, DISK 2, DISK 3, [...] DISK 10 I'm out of collations to try now according to SELECT * FROM pg_collation;... unless I'm supposed to use one of the many really weird ones with cryptic names. (I even tried a bunch of those with the same result.) Please note that I've read the existing seemingly related SE questions as well as many articles on SORT BY, but they didn't help and didn't clear up anything for me. I'm using PostgreSQL 12.4

*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 **number**, which is formatted with 4-digits year, a dash (-), and a progressive numeric value. For example it may contain the following values: `SELECT number FROM orders ORDER BY number LIMIT 10;` number ---------- 1999-13 2019-11 2020-1 2020-10 2020-100 2020-12 2020-2 2020-21 2020-3 2021-1 I need to sort that field by year and then by the progressive number, with this expected result: number ---------- 1999-13 2019-11 2020-1 2020-2 2020-3 2020-10 2020-12 2020-21 2020-100 2021-1 A DB fiddle is here . My questions are: 1) Which is the simplest way to achieve this with an ORDER subclause? 2) How to add an efficient index using this custom sort without having to modify the table? I would like to keep at least the first answer as database-agnostic as possible (that's why I haven't included db specific tags), but if different best answers are possible for different DBMS/versions, let's assume PostgreSQL 12.

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 this query: zoology=# SELECT name FROM animal ORDER BY name; name ----------------- 0_Zebra Antelope Piranha Rhino _Starts With 1 _Starts With 1 Starts With 1 __Starts With 2 (8 rows) Notice how the rows are sorted in an order that implies the leading _ is used to place the _Starts With 1 rows before the Starts row, but the __ in the __Starts With 2 seems to ignore this fact, as if the 2 at the end is more important than the first two characters. Why is this? If I sort with Python, the result is: In : for animal in sorted(animals): ....: print animal ....: 0_Zebra Antelope Piranha Rhino Starts With 1 _Starts With 1 _Starts With 1 __Starts With 2 Furthermore, Python ordering suggests that underscores come *after* letters, which indicates that the Postgres's sorting of the first two _Starts rows before the Starts row is incorrect. Note: I'm using Postgres 9.1.15 Here are my attempts at finding the collation: zoology=# select datname, datcollate from pg_database; datname | datcollate -----------+------------- template0 | en_US.UTF-8 postgres | en_US.UTF-8 template1 | en_US.UTF-8 zoology | en_US.UTF-8 (4 rows) And: zoology=# select table_schema, table_name, column_name, collation_name from information_schema.columns where collation_name is not null order by table_schema, table_name, ordinal_position; table_schema | table_name | column_name | collation_name --------------+------------+-------------+---------------- (0 rows)

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 (such as upcoming birthdays), how can the results be sorted by month in the correct chronological order for the selected time range? For example, if the query grabs all dates between November 20th and February 20th regardless of year, it should sort them in the order: Nov -> Dec -> Jan -> Feb But a regular sort by month actually produces: Jan -> Feb -> Nov -> Dec Or an inverted sort produces: Dec -> Nov -> Feb -> Jan both of which are wrong. Here's a sample query with a three day range at the end of the year: SELECT EventDate FROM Events WHERE TIMESTAMPDIFF(YEAR, EventDate, "2012-12-28" + INTERVAL 3 DAY) > TIMESTAMPDIFF(YEAR, EventDate, "2012-12-28" - INTERVAL 1 DAY) ORDER BY DATE_FORMAT(EventDate, "%m-%d") DESC It produces: 2012-01-01 2008-01-01 2008-01-01 1987-01-02 1994-12-28 Where it should produce: 1994-12-28 2012-01-01 2008-01-01 2008-01-01 1987-01-02 Is there a way to get it to sort by month and day in a "going forward from range start" order? I searched here and on SO but didn't find an answer that dealt with both year end wrapping of months and a scenario where the year in the date field isn't relevant for sorting. **Update**: The range should always sort forward, so if the range is Feb to Nov, it does a standard sort of Feb - > Mar -> Apr .... If the range is Nov to Feb, it sorts Nov -> Dec -> Jan .... Although I don't anticipate that being an issue since I don't need to query more than a 3 month window.

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, ','::text ORDER BY (t_ligne.ligne_code::text)) AS lignes My trouble with the ordering: I need an alphanumeric order like: "09,12,20,90,91,92,200,217,400,500,Exp3" If I try a CAST, it refuses to accept text "numbers" like "Exp3". So I didn't find any way to order it like values with text objects at the end. EDIT I don't know how to use WITH in my query without cutting my results! In fact, this is the total query: SELECT l_arret_ligne.idap, t_arret.nom_arret AS nom, string_agg(DISTINCT t_ligne.ligne_code::text, ','::text ORDER BY (t_ligne.ligne_code::text)) AS lignes FROM l_arret_ligne, t_ligne, t_arret WHERE l_arret_ligne.id_ligne::text = t_ligne.id_ligne::text AND l_arret_ligne.idap::text = t_arret.idap::text AND t_ligne.ligne_type::text 'Scolaire'::text GROUP BY l_arret_ligne.idap, t_arret.nom_arret As my string_agg is inside a global query, I don't understand how it would be possible to cut it with WITH and globalize it at the end. Maybe do I understand bad... Thanks again! EDIT 2: Hello again! I found one very good solution to order my results: SELECT string_agg("rla"."t_ligne_19032019"."ligne_code", ',' ORDER BY case when (UPPER(ligne_code)=LOWER(ligne_code))=true then substring(concat('000000000000000',ligne_code),length(ligne_code)+1,15) else ligne_code end) AS lignes FROM "rla"."t_ligne_19032019" It works very well. BUT always the same trouble with DISTINCT! If I add DISTINCT in the string_agg, same message: > ERROR: in an aggregate with DISTINCT, ORDER BY expressions must > appear in argument list Any solution? Any advice? Thanks again! Cyril

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 letters and numbers... (on Oracle 11r2) I get my data sorted the opposite way: numbers, capital letters, small letters (Oracle 12c) This is realy confusing the end-user of various applications querying the database. What settings do I need to check/compare to fix this problem in Oracle 12c? The queries I refer to have an order by clause. **EDIT** The settings on for these parameters are the same on both evironments: **NLS_SORT** type: string value: **nls_language** type: string value: DUTCH So there is no value for NLS_SORT, what is the default then? @phil select * from NLS_INSTANCE_PARAMETERS; Parameter Oracle11r2 Oracle12c NLS_LANGUAGE DUTCH DUTCH NLS_TERRITORY THE NETHERLANDS THE NETHERLANDS NLS_SORT NLS_DATE_LANGUAGE NLS_DATE_FORMAT NLS_CURRENCY NLS_NUMERIC_CHARACTERS NLS_ISO_CURRENCY NLS_CALENDAR NLS_TIME_FORMAT NLS_TIMESTAMP_FORMAT NLS_TIME_TZ_FORMAT NLS_TIMESTAMP_TZ_FORMAT NLS_DUAL_CURRENCY NLS_COMP BINARY BINARY NLS_LENGTH_SEMANTICS BYTE CHAR NLS_NCHAR_CONV_EXCP FALSE FALSE select * from NLS_SESSION_PARAMETERS; these are the same as my NLS settings in Oracle SQL developer, so not very relevant I guess... select * from NLS_DATABASE_PARAMETERS; These are the same, only NLS_RDBMS_VERSION differs So only NLS_LENGTH_SEMANTICS differs. Is there a way to check the values for other sessions?

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.