Order by result of subquery in PostgreSQL

postgres subquery as column
postgres join on subquery
order by subquery
postgres order by subquery
order by in subquery oracle
postgresql multiple subqueries
postgres subquery multiple columns
postgresql subquery vs join

Assuming I have one table Employees with the columns id, name, salary and manager_id and another table fields with the column field which can be any of the fields in the Employees table.

How can I sort the employees by the rows in the fields table? For example: when fields contains the values 'salary', 'manager_id', the employees will be sorted by salary and then by manager_id.

I tried something like this but it didn't work:

SELECT * FROM employees ORDER BY (SELECT field FROM fields)

Edit: The original question was a simplified example of my goal. I want that the employees will be sorted by their super manager id, then by the second super manager id...and in the end by their direct manager’s id.

Given the employees(id, name, salary, manager_id): 1 Alex 1000 NULL 2 Mor 2000 1 3 John 3000 NULL 4 Chris 4000 1 5 Michael 5000 4 6 Matt 6000 2 The query result will be: 1 Alex 1000 NULL 2 Mor 2000 1 6 Matt 6000 2 4 Chris 4000 1 5 Michael 5000 4 3 John 3000 NULL


You cannot do that in a single query.

First you have to query fields, then construct an SQL statement with the proper ORDER BY clause and run that.

Beware of SQL injection — use the format function to construct the query.

Why is ORDER BY in a FROM Subquery Ignored?, A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified. Introduction to PostgreSQL ORDER BY clause. When you query data from a table, PostgreSQL returns the rows in an unspecified order. To sort the result set, you use the ORDER BY clause in the SELECT statement. The ORDER BY clause allows you to sort rows returned from a SELECT statement in ascending or descending order based on the specified criteria.


If you can tell us the error that it gives you it may help us helping you

what i think is that ' symbols are what making the issue so it will be as if you're writing:

SELECT * FROM employees ORDER BY 'salary', 'parent_id'

try replacing it with a blank character using Replace()

Ordering in subqueries, In Postgres, you could also use the non-standard DISTINCT ON syntax to achieve the same result. This is more flexible, as you can have the  A subquery may occur in: In PostgreSQL subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement or inside another subquery. A subquery is usually added within the WHERE Clause of another SQL SELECT statement. You can use the comparison operators, such as >, <, or =.


There is a way how this can be accomplished. But i strongly advice to change your design. To support this you must add SEQ field in your Fields table to decide order of fields in Fields table. First field have SEQ 1, second 2 ...

SELECT
    *
FROM
    Employees E
ORDER BY
    CASE 
    (SELECT
        F.NAME
    FROM
        Fields F ORDER BY F.SEQ LIMIT 1) 
    WHEN 'salary' THEN E.salary
    WHEN 'parent_id' THEN E.parent_id
    ELSE 0 END

    ,
    CASE 
    (SELECT
        F.NAME
    FROM
        Fields F ORDER BY F.SEQ LIMIT 1 OFFSET 1) 
    WHEN 'salary' THEN E.salary
    WHEN 'parent_id' THEN E.parent_id
    ELSE 0 END

sample on sql fiddle to demonstrate. There are two tables FieldsA and FieldsB to make testing easier without need for delete from table Fields and new records to see if it is working. http://sqlfiddle.com/#!15/64df6e/2/0

PostgreSQL Subqueries, The main query (outer query) use the subquery result. PostgreSQL Subquery Example: Using a subquery, list the name of the employees, paid  > WHERE o.date_order BETWEEN '2010-01-01' AND '2010-01-31' > GROUP BY i.id_item, i.price) > ); > > No luck. Obviously SUM expects an expression, not a set of rows. Is > there a way to perform a sum of the resulting rows? > I don't have a PostgreSQL server to try this right now. But you are looking for something like SELECT SUM (cost) from


SQL - Sub Queries, the comparison operators, such as >, <, or =. PostgreSQL executes the query that contains a subquery in the following sequence: First, executes the subquery. Second, gets the result and passes it to the outer query. Third, executes the outer query. PostgreSQL subquery with IN operator. A subquery can return zero or more rows. To use this subquery, you use the IN operator in the WHERE clause.


ORDER BY is not allowed in SUBQUERY – 1Z0-061 Oracle SQL, , inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> </head>


Why we can't put Order by inside the view?, Use the result of the first query in the second SELECT statement to find the films that we want. The following query gets the average rental rate: SELECT AVG (  The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is "true" ; if the subquery returns no rows, the result of EXISTS is "false".