Order by result of subquery in PostgreSQL
Assuming I have one table
Employees with the columns
and another table
fields with the column
field which can be any of the fields in the
How can I sort the employees by the rows in the
For example: when
fields contains the values
'salary', 'manager_id', the employees will be sorted by
salary and then by
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
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
FieldsB to make testing easier without need for delete from table Fields and new records to see if it is working.
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".