Use Column Values in ORDER BY and LIMIT Clauses in PostgreSQL

order-bypostgresql

I have this table in a Postgres 9.3 database:

I need to use the content of columns order and limit to sort and filter this table. The table will be sorted by column first_name.

The final result will be as in the following picture:

Note: Sorry if it is simple for you, but I can't solve this. All mail addresses were generated by www.mockaroo.com. If your address is in this list, don't blame me.

order and limit columns will always have same data. order may be asc or desc and limit may be any integer value (but all rows will be the same value. It came from a grouping query.

Best Answer

Query

While your solution is clever, it's poison for performance because the value to order by has to be computed separately for every row. More importantly, your query cannot use a plain index.

I suggest to move the trick to the LIMIT clause and use UNION ALL. This way the query gets cheaper overall and can use an index (which nukes competitors that can't).

WITH l AS (
   SELECT CASE WHEN ordr = 'a' THEN lim ELSE 0 END AS lim_a
        , CASE WHEN ordr = 'd' THEN lim ELSE 0 END AS lim_d
   FROM   test
   LIMIT  1
   )
(SELECT * FROM test ORDER BY first_name      LIMIT (SELECT lim_a FROM l))
UNION ALL
(SELECT * FROM test ORDER BY first_name DESC LIMIT (SELECT lim_d FROM l));

One of both SELECT gets LIMIT 0 and is never executed. You'll see "never executed" in the EXPLAIN ANALYZE output.

About twice as fast without index in a quick test on Postgres 9.4 (35 rows out of 50k), but several orders of magnitude faster with index. The difference grows with the size of the table, obviously.

Table layout

Don't use reserved key words as identifier (as you found yourself already): lim and ordr instead of ~~limit~~ and ~~order~~.

Don't bloat your table with redundant values. If you cannot avoid storing lim and ordr for every row, at least make it small. Basic layout:

CREATE TABLE test (
  id         int
, lim        int
, ordr       "char" CHECK (ordr IN ('a', 'd'))
, first_name text
, email      text
);

"char" is perfect as a simplistic enumeration type and occupies just 1 byte.
Create an index:

CREATE INDEX test_first_name_idx ON test(first_name);

Details:

SQL Fiddle.

Related Solutions

Mysql – How to do a complex GROUP BY in MySQL

What about doing a little math against your ID column to dynamically generate the group?

SELECT grp, FLOOR(id/10) AS id_grp
FROM animals
GROUP BY grp, id_grp

This would give you groups of 10 based on the ID of the record. I used your animals table above to generate the data below.

Sample data

 INSERT INTO animals VALUES
 ('mammal',10,'dog'),('mammal',11,'dog'),('mammal',12,'dog'),
 ('mammal',21,'cat'),('mammal',22,'cat'),('mammal',23,'cat'),
 ('mammal',24,'cat'),('mammal',25,'cat'),('mammal',26,'cat'),
 ('bird',30,'penguin'),('bird',31,'penguin'),('bird',32,'penguin'),
 ('bird',33,'penguin'),('fish',44,'lax'),('fish',45,'lax'),
 ('fish',46,'lax'),('fish',47,'lax'),('fish',48,'lax'),
 ('mammal',31,'whale'),*'fish',51,'lax'),('fish',52,'lax'),
 ('fish',53,'lax'),('fish',54,'lax'),('bird',10,'ostrich');

Query Output

 +--------+--------+
 | grp    | id_grp |
 +--------+--------+
 | fish   |      4 |
 | fish   |      5 |
 | mammal |      1 |
 | mammal |      2 |
 | mammal |      3 |
 | bird   |      1 |
 | bird   |      3 |
 +--------+--------+
 7 rows in set (0.00 sec)

MySQL – Using Index for Both ASC and DESC on String Column

Although the syntax for CREATE INDEX supports an option for defining the index as ascending or descending, in the most common two storage engines (InnoDB and MyISAM), this index option is a no-op. There is no such thing as an "ascending" or "descending" index, they are just indexes and can be used for sorting in either direction.

But in the case you describe, where you have a multi-column index and you want to order by one column ascending and the other column descending, that's a different story. The sort order must go in the same direction for both columns if it uses an index.

(Not that every sort has to use an index. You can certainly run the query and let it sort matching rows with a filesort.)

To reverse the sort order of strings, you could add your own custom collation. https://dev.mysql.com/doc/refman/5.6/en/adding-collation.html

But you'd have to store your strings using that new collation, and rebuild your index. The index would only be useful for the reverse-sorting.

Re comment from @Cratylus:

A collation is what defines "A" as being before "B" and all the other ordering of letters. So if you define a collation that says that "B" is before "A", and use that collation in your column definition, then sorting on that column, or creating an index on the column, would be in the reverse order.

Note: the following doesn't create a proper collation, it just demonstrates the steps to create one.

Edit /usr/share/mysql/charsets/Index.xml and make this edit:

<charset name="latin1">

  . . . leave other collations alone . . .

  <collation name="latin1_reverse_ci" id="251">
    <order>Dutch</order>
    <order>English</order>
    <order>French</order>
    <order>German Duden</order>
    <order>Italian</order>
    <order>Latin</order>
    <order>Portuguese</order>
    <order>Spanish</order>
  </collation>

</charset>

Edit /usr/share/mysql/charsets/latin1.xml and make this edit:

<charset name="latin1">

. . . leave other collations alone . . .

<collation name="latin1_reverse_ci">
<map>
 AE B1 AC A2 A0 9E 9C 8D BE 8B 89 87 85 83 7F 59
 73 71 6F 6D 63 61 5F 5D 55 4F 4D 4B 49 47 45 43
 97 B1 AC A2 A0 9E 9C 8D BD 8B 89 87 85 83 7F 59
 73 71 6F 6D 63 61 5F 5D 55 4F 4D 4B 49 47 45 43
 FF FE FD FC FB FA F9 F8 F7 F6 F5 F4 F3 F2 F1 F0
 EF EE ED EC EB EA E9 E8 E7 E6 E5 E4 E3 E2 E1 E0
 DF DE DD DC DB DA D9 D8 D7 D6 D5 D4 D3 D2 D1 D0
 CF CE CD CC CB CA C9 C8 C7 C6 C5 C4 C3 C2 C1 C0
 BF BC BB BA B9 AF AA A8 A6 A4 9A 98 95 93 91 8F
 81 7D 7B 79 77 75 6B 69 67 65 5B 57 53 51 41 B8
 B7 B6 B5 B4 B3 AF AA A8 A6 A4 9A 98 95 93 91 8F
 81 7D 7B 79 77 75 6B 69 67 65 5B 57 53 51 41 40
 3F 3E 3D 3C 3B 3A 39 38 37 36 35 34 33 32 31 30
 2F 2E 2D 2C 2B 2A 29 28 27 26 25 24 23 22 21 20
 1F 1E 1D 1C 1B 1A 19 18 17 16 15 14 13 12 11 10
 0F 0E 0D 0C 0B 0A 09 08 07 06 05 04 03 02 01 00
</map>
</collation>

</charset>
. . .

Restart mysqld.

Confirm you have the new collation:

mysql> SHOW COLLATION LIKE 'latin1%';
+---------------------+---------+-----+---------+----------+---------+
| Collation           | Charset | Id  | Default | Compiled | Sortlen |
+---------------------+---------+-----+---------+----------+---------+
. . .
| latin1_general_ci   | latin1  |  48 |         | Yes      |       1 |
| latin1_general_cs   | latin1  |  49 |         | Yes      |       1 |
| latin1_spanish_ci   | latin1  |  94 |         | Yes      |       1 |
| latin1_reverse_ci   | latin1  | 251 |         |          |       0 |
. . .

Create a table and fill it with some text:

mysql> CREATE TABLE foo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    t VARCHAR(60) COLLATE latin1_reverse_ci, 
    x DATE,
    KEY(t)
);

mysql> INSERT INTO foo (t) VALUES ('Harry'), ('Ron'), ('Hermione');

Demonstrate that the sort order is the reverse of what you'd think it should be:

mysql> SELECT * FROM foo ORDER BY t ASC;
+----+----------+------+
| id | t        | x    |
+----+----------+------+
|  2 | Ron      | NULL |
|  3 | Hermione | NULL |
|  1 | Harry    | NULL |
+----+----------+------+

Confirm that it doesn't use a filesort:

mysql> EXPLAIN SELECT * FROM foo WHERE t LIKE 'H%' ORDER BY t\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
   partitions: NULL
         type: range
possible_keys: t
          key: t
      key_len: 63
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

I had to put a condition on the rows, because otherwise it thought it was going to read all the rows in the table, and it did a table-scan and filesort.

Re comment from @ypercube:

It doesn't reverse the order of characters in a given string like REVERSE(), it reverses which characters are considered greater than or less than each other when comparing two strings. So no, the CHAR padding spaces don't matter, they're still at the end of both strings.

Example (no index this time, just using order by):

mysql> create table t (c char(20) collate latin1_reverse_ci);
mysql> insert into t values ('A'), ('B'), ('C');
mysql> select * from t order by c;
+------+
| c    |
+------+
| C    |
| B    |
| A    |
+------+

Edit: no, @ypercube is correct, this doesn't work with CHAR because the space character is in the wrong place when I naively reorder the collation simply by putting all the bytes in the reverse order. That's not the proper way to design a collation.

So this answer should serve as a demo of the mechanics of creating a collation, not as a proper collation for reversing alphabetical order.