1

How to order alphabetically and have results with a dot on the first place?

Query:

SQL SELECT name FROM articles ORDER BY name;

**and the result is:**

GitFlow GithubFlow .gitignore Handover Incremental migrations


...but I want to have `.gitignore` first.

**Expected result:**

.gitignore GitFlow GithubFlow Handover Incremental migrations





**//update:**

**Correct query is:**
SQL
SELECT name FROM articles ORDER BY name::bytea;

source: Incorrect sort/collation/order with spaces in Postgresql 9.4

...but I don't know why, could you explain it?

//edit1:

I need to sort other non-letter characters as first too. (e.g. $-_# ...)

Egon Allison
  • 1,329
  • 1
  • 13
  • 22
Prokop Simek
  • 312
  • 1
  • 12
  • It's likely a collation ordering issue, out of interest why are you casting the column to the bytea data type? – Lucas Mar 04 '19 at 15:17
  • @Lucas I found this issue: https://stackoverflow.com/questions/34537982/incorrect-sort-collation-order-with-spaces-in-postgresql-9-4/34538337#34538337 so it's a try. – Prokop Simek Mar 04 '19 at 15:24
  • Have you tried the query without casting it to bytea? – Lucas Mar 04 '19 at 15:33
  • @Lucas of course I tried... As you can see the paragraph with "Query" and "and the result is"... It's the default behaviour of PostgreSQL on Heroku and I don't know why. :) – Prokop Simek Mar 05 '19 at 07:03
  • I was asking about Bytea because has no collation (rules of ordering) it is ordered on byte value whereas "string" (I can only assume this was the column type that you were casting, ruling out enum, json etc...) will have a collation, hence the strange ordering. Fixing a problem requires understanding it. – Lucas Mar 05 '19 at 08:00

2 Answers2

0

Using

ORDER BY
  CASE
   WHEN SUBSTRING(name, 1, 1) = '.'
   THEN 1 
   ELSE 2
  END

feels more "natural".

Edited

But I need to sort other characters as first either. E.g. "#, $, _, -" etc.

Then you can try.

ORDER BY
  CASE
   WHEN
        LOWER(SUBSTRING(name, 1, 1)) NOT BETWEEN 'a' AND 'z'
      AND
        SUBSTRING(name, 1, 1) NOT BETWEEN '0' AND '9'
   THEN 1 
   ELSE 2
  END
Raymond Nijland
  • 11,488
  • 2
  • 22
  • 34
  • But I need to sort other characters as first either. E.g. "#, $, _, -" etc. – Prokop Simek Mar 05 '19 at 07:03
  • It seems useful! :) And it's all about collation? How can I find differences between my db and the Heroku's db to see e.g. the collation difference? – Prokop Simek Mar 05 '19 at 11:21
  • "How can I find differences between my db and the Heroku's db to see e.g. the collation difference?" i would check the `information_schema` database views like [schemata](https://www.postgresql.org/docs/current/infoschema-schemata.html) , [tables](https://www.postgresql.org/docs/current/infoschema-tables.html) and [columns](https://www.postgresql.org/docs/current/infoschema-columns.html) there should be charset and collation information in there. – Raymond Nijland Mar 05 '19 at 11:43
  • "And it's all about collation?" Also i pretty sure most collations which PostgreSQL supports the ascii subset (for example the UTF8 charset does support ascii charset).. Because a-z, 0-9 is defined as ascii i believe this query should work fine for the common used charsets and collations @ProkopSimek – Raymond Nijland Mar 05 '19 at 11:45
0

I'd expect that the following will meet your needs best:

ORDER BY
   NOT starts_with(name, '.'),
   name COLLATE "C"

This will sort "dot files" first because FALSE < TRUE, and within these groups the names are sorted in binary order (e.g., upper case letters before lower case letters).

My COLLATE "C" has the same effect as your cast, except it is cheaper.

Your query achieves what you want (to some extent) because the ASCII value for . is less than the ASCII value of all letters.

If you would rather have the names sorted in natural language order (e.g. albe < Simek), remove the COLLATE clause in my answer.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263