I am new to Postgres & I am facing difficulty in this query:
I have this query:
SELECT "employees_employee"."primary_title",
"employees_employee"."primary_role_id",
"employees_organization"."benchmark_organization_id" AS "benchmark_organization",
COUNT("employees_employee"."id") FILTER (WHERE "employees_employee"."role_mapping_id" IS NULL) AS "count",
ROW_NUMBER() OVER (PARTITION BY "employees_organization"."benchmark_organization_id", "employees_employee"."primary_role_id" ORDER BY COUNT("employees_employee"."id") FILTER (WHERE "employees_employee"."role_mapping_id" IS NULL) DESC) AS "rank"
FROM "employees_employee"
LEFT OUTER JOIN "employees_job"
ON ("employees_employee"."primary_job_id" = "employees_job"."id")
LEFT OUTER JOIN "employees_organization"
ON ("employees_job"."organization_id" = "employees_organization"."id")
WHERE ("employees_employee"."blueprint_id" = '58b67b16-6890-40d6-8583-210c81647230'::uuid AND "employees_employee"."deleted" IS null)
GROUP BY "employees_employee"."primary_title",
"employees_organization"."benchmark_organization_id",
"employees_employee"."primary_role_id"
The above query give me data something like this:
primary_title |primary_role_id |benchmark_organization |count|rank|
----------------------------------------------------------------------|------------------------------------|------------------------------------|-----|----|
manager |d56aae96-cc70-4ef7-8db9-a6aa85f1a93c|be3555cc-aace-463a-9095-f11fe6e0a6fc| 1| 1|
Unknown Title | |be3555cc-aace-463a-9095-f11fe6e0a6fc| 1| 1|
consultant |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 262| 1|
associate consultant |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 230| 2|
senior associate consultant |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 67| 3|
associate consultant intern |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 21| 4|
incoming associate consultant |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 16| 5|
management consultant |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 10| 6|
senior consultant |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 8| 7|
junior consultant |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 2| 8|
specialist consultant |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 2| 9|
incoming associate consultant intern |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 2| 10|
consulting |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 2| 11|
recruitment consultant |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 12|
director of global consultant talent management |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 13|
industry consultant |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 14|
consultant (private equity group) |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 15|
alliance partner - bulgaria | management consulting |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 16|
associate consultant intern (aci) |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 17|
lenox conyngham scholar at cambridge and incoming associate consultant|0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 18|
practice area consultant in peg automation at bain consulting |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 19|
practice area consultant |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 20|
consultor |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 21|
head of talent and consulting ops, nyc |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 22|
case team leader |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 23|
freelance associate consultant |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 24|
associate consultant ii |0b15c7bd-ce3d-4741-b219-77f15a46df09| | 1| 25|
Basically i am just tring to pull the count of employees with a primary_title for each primary_role & benchmark_organization combo. The twist is that i just need the top 10 roles job_titles count for each category (primary_role & benchmark_organization combo)
For this my approach was to add a rank to each category data and filter the final data on rank column.
Note that the columns count & rank are aliased columns and values are calculated at runtime.
I would just like to filter out the records where rank is less than or equal to 10.
This is as simple as adding that to a where clause in Mysql or some other RDBMS, but looks like postgres does not support aliased columns in where clause.
Is there any alternative way to filter the result ?
