Postgresql - Order by an Array

ORDER BY the IN value list

You can do it quite easily with (introduced in PostgreSQL 8.2) VALUES (), ().

Syntax will be like this:

select c.*
from comments c
join (
  values
    (1,1),
    (3,2),
    (2,3),
    (4,4)
) as x (id, ordering) on c.id = x.id
order by x.ordering

How to respect the order of an array in a PostgreSQL select sentence

For long arrays you typically get (much!) more efficient query plans with unnesting the array and joining to the main table. In simple cases, this even preserves the original order of the array without adding ORDER BY. Rows are processed in order. But there are no guarantees and the order may be broken with more joins or with parallel execution etc. To be sure, add WITH ORDINALITY:

CREATE OR REPLACE FUNCTION list_products (tid int[])  -- VARIADIC?
  RETURNS TABLE (
   id        integer,
   reference varchar,
   price     decimal(13,4)
   )
  LANGUAGE sql STABLE AS
$func$
  SELECT product_id, p.reference, p.price
  FROM   unnest(tid) WITH ORDINALITY AS t(product_id, ord)
  JOIN   product p USING (product_id)  -- LEFT JOIN ?
  ORDER  BY t.ord
$func$;

Fast, simple, safe. See:

PostgreSQL unnest() with element number
Join against the output of an array unnest without creating a temp table

You might want to throw in the modifier VARIADIC, so you can call the function with an array or a list of IDs (max 100 items by default). See:

Return rows matching elements of input array in plpgsql function
Call a function with composite type as argument from native query in jpa
Function to select column values by comparing against comma separated list

I would declare STABLE function volatility.

You might use LEFT JOIN instead of JOIN to make sure that all given IDs are returned - with NULL values if a row with given ID has gone missing.

db<>fiddle here

Note a subtle logic difference with duplicates in the array. While product_id is UNIQUE ...

unnest + left join returns exactly one row for every given ID - preserving duplicates in the given IDs if any.
product_id = any (tid) folds duplicates. (One of the reasons it typically results in more expensive query plans.)

If there are no dupes in the given array, there is no difference. If there can be duplicates and you want to fold them, your task is ambiguous, as it's undefined which position to keep.

PostgreSQL - order by an array

CREATE OR REPLACE FUNCTION search_by_tags(tags varchar[])
  RETURNS TABLE (id_course integer, name text, tag_ct integer)
  LANGUAGE sql AS
$func$
   SELECT id_course, c.name, ct.tag_ct
   FROM  (
      SELECT tc.id_course, count(*)::int AS tag_ct
      FROM   unnest($1) x(tag)
      JOIN   tagcourse tc USING (tag)
      GROUP  BY 1                      -- first aggregate ..
      ) AS ct
   JOIN   course c USING (id_course)   -- .. then join
   ORDER  BY ct.tag_ct DESC            --  more columns to break ties?
$func$;

Use unnest() to produce a table from your input array, like already demonstrated by @Clodoaldo.

You don't need plpgsql for this. Simpler with a plain SQL function.

I use unnest($1) (with positional parameter) instead of unnest(tags), since the later is only valid for PostgreSQL 9.2+ in SQL functions (unlike plpgsql). The manual:

In the older numeric approach, arguments are referenced using the
syntax $n: $1 refers to the first input argument, $2 to the second,
and so on. This will work whether or not the particular argument was
declared with a name.

count() returns bigint. You need to cast it to int to match the declared return type or declare the the returned column as bigint to begin with.

Perfect occasion to simplify the syntax a bit with USING (equi-joins): USING (tag) instead of ON tc.tag = c.tag.

It's regularly faster to first aggregate, then join to another table. Reduces the needed join operations.

To address @Clodoaldo's comments, here is a fiddle demonstrating the difference:

db<>fiddle here

_{Old sqlfiddle}

OTOH, if you aggregate after the join, you don't need a subquery. Shorter, but probably slower:

SELECT c.id_course, c.name, count(*)::int AS tag_ct
FROM   unnest($1) x(tag)
JOIN   tagcourse tc USING (tag)
JOIN   course     c USING (id_course)
GROUP  BY 1
ORDER  BY 3 DESC;  --  more columns to break ties?

Postgresql 11 - Order by integer array index

You can use array_position():

ORDER BY array_position('{3,2,1}'::int[], id)

If you didn't want to repeat the array twice:

select p.id
from product p join
     (values ('{3,2,1}'::int[])) v(ar)
     on p.id = any(v.ar)
order by array_position(v.ar, p.id);

Sort array elements alphabetically PostgreSQL

To do this you will need to use the ORDER BY clause in the ARRAY_AGG function.
Example:

SELECT ARRAY_AGG(fullname ORDER BY lastname)
FROM ...

This will return an array with names sorted by last name.

Order by number of matches in array

Unnest tags, filter unnested elements and aggregate remaining ones:

select id, count(distinct u) as matches
from (
    select id, u
    from testing_items,
    lateral unnest(tags) u
    where u in ('222', '555', '666')
    ) s
group by 1
order by 2 desc

 id | matches 
----+---------
  5 |       3
  3 |       2
  2 |       1
  4 |       1
(4 rows)

Considering all the answers, it seems that this query combines good sides of each of them:

select id, count(*) 
from testing_items,
unnest(array['11','5','8']) u
where tags @> array[u] 
group by id 
order by 2 desc, 1;

It has the best performance in Eduardo's test.

PostgreSQL - ORDER BY a date in nested array

You have all the necessary information in your images table. (Since you did not show the definition of that table, what follows is assuming a couple of things.) A window function should do the trick:

WITH image_tags AS (
    SELECT images."post id", 
           json_build_object (
               'imageID', images."image id",
               'uploaded', images.uploaded,
               'tags', json_agg("tag map".tag) ) AS image,
           first_value(images.uploaded) OVER (
               PARTITION BY images."post id" ORDER BY images.uploaded DESC
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS newest,
    FROM images
    JOIN "tag map" ON images."image id" = "tag map"."image id"
    GROUP BY images."post id", images."image id"
    ORDER BY images."pos" DESC
)
SELECT posts."post id" AS "postID", 
       json_agg(image_tags.image) AS images
FROM posts
JOIN image_tags USING ("post id")
GROUP BY posts."post id"
ORDER BY image_tags.newest -- ASC by default, use DESC for reverse order

The window function takes all the rows in the images table that have the same "post id" and makes them available for analysis. You can then find the first and the last row of the window frame and store the value in new columns in the image_tags row source. After that the ordering in the main query is straightforward.