CODEWITHSUNDEEP
sqlpostgresql
sitoNz
sitoNz

Reputation: 93

Order by result of subquery in PostgreSQL

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

Upvotes: 1

Views: 1740

Answers (3)

Livius
Livius

Reputation: 956

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

Upvotes: 0

Tonner Mààn
Tonner Mààn

Reputation: 133

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()

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247625

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.

Upvotes: 0

Related Questions