PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – If I have a priority list, how do I select the middle value in SQL?

I have a couple of organizations in my company that has associated "group categories" assigned to them with individual priority_level’s. I have a query with

WHERE gc.priority_level = (SELECT MIN(gc_inner.priority_level) ...), as first_category

and

WHERE gc.priority_level = (SELECT MAX(gc_inner.priority_level) ...), as third_category

the gc priority levels can range from organization to organization, some only have 1 priority level and others have a max of 3, so I need to be able to find the potential second_category that would be between the lowest and highest priority levels.

(             
  SELECT g.name             
  FROM groups g             
  INNER JOIN group_categories gc ON gc.id = g.group_category_id             
  INNER JOIN item_groups ig ON ig.group_id = g.id AND ig.item_id = i1."id"             
  WHERE gc.priority_level = 
  (               
     SELECT MIN(gc_inner.priority_level)               
     FROM group_categories gc_inner               
     INNER JOIN item_groups ig_inner ON ig_inner.group_id = g.id 
     AND ig_inner.item_id = i1."id"               
     WHERE gc_inner.id = g.group_category_id AND gc_inner.type = 'item'             
  )                  
  AND gc.type = 'item'             
  LIMIT 1           
) AS "primary_item_category",             
(             
  SELECT g.name             
  FROM groups g             
  INNER JOIN group_categories gc ON gc.id = g.group_category_id             
  INNER JOIN item_groups ig ON ig.group_id = g.id 
  AND ig.item_id = i1."id"             
  WHERE gc.priority_level = 
  (               
    SELECT MIN(gc_inner.priority_level)               
    FROM group_categories gc_inner               
    INNER JOIN item_groups ig_inner ON ig_inner.group_id = g.id 
    AND ig_inner.item_id = i1."id"               
    AND gc_inner.priority_level > 
    (                 
      SELECT MIN(gc_inner2.priority_level)                 
      FROM group_categories gc_inner2                 
      INNER JOIN item_groups ig_inner2 ON ig_inner2.group_id = g.id 
      AND ig_inner2.item_id = i1."id"               
    )
  )             
  AND gc.type = 'item'             
  LIMIT 1           
) AS "second_item_category", 
(             
  SELECT g.name             
  FROM groups g             
  INNER JOIN group_categories gc ON gc.id = g.group_category_id             
  INNER JOIN item_groups ig ON ig.group_id = g.id 
  AND ig.item_id = i1."id"             
  WHERE gc.priority_level = 
  (               
    SELECT MAX(gc_inner.priority_level)               
    FROM group_categories gc_inner               
    INNER JOIN item_groups ig_inner ON ig_inner.group_id = g.id 
    AND ig_inner.item_id = i1."id"
  )             
  AND gc.type = 'item'             
  LIMIT 1           
) AS "third_item_category",
...

I set this up in my test:

org = insert(:organization)
  group_category1 =     insert(:group_category, type: "organization", priority_level: 1, organization: org, name: "OrganizationCategory")    
  group_category2 =     insert(:group_category, type: "item", priority_level: 2, organization: org, name: "PrimaryItemCategory")    
  group_category3 =     insert(:group_category, type: "item", priority_level: 3, organization: org, name: "SecondItemCategory")    
  group_category4 =     insert(:group_category, type: "item", priority_level: 4, organization: org, name: "ThirdItemCategory")    

  org_group1 = Factory.insert(:group, group_category: group_category1, name: "org_group1")   
  org_group2 = Factory.insert(:group, group_category: group_category2, name: "org_group2")   
  org_group3 = Factory.insert(:group, group_category: group_category3, name: "org_group3")   
  org_group4 = Factory.insert(:group, group_category: group_category4, name: "org_group4")

So I’m expecting a return of ‘org_group3’ but instead I’m getting nil when pulling for second_item_category, but all other categories return correctly.

If I change the second_item_category to

(
  SELECT g.name             
  FROM groups g             
  INNER JOIN group_categories gc ON gc.id = g.group_category_id             
  INNER JOIN item_groups ig ON ig.group_id = g.id AND ig.item_id = i1."id"             
  WHERE gc.priority_level = 
  (               
    SELECT priority_level               
    FROM 
    (                 
      SELECT 
        gc_inner.priority_level, 
        ROW_NUMBER() OVER (ORDER BY gc_inner.priority_level) AS rn,                       
        COUNT(*) OVER () AS cnt                 
      FROM group_categories gc_inner                 
      INNER JOIN item_groups ig_inner ON ig_inner.group_id = g.id AND ig_inner.item_id = i1."id"                 
      WHERE gc_inner.id = g.group_category_id AND gc_inner.type = 'item'               
    ) subquery               
    WHERE rn = (cnt + 1) / 2             
  )             
  AND gc.type = 'item'             
  LIMIT 1           
) AS "second_item_category",

then it returns org_group2 instead of org_group3.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I was able to get it working with LIMIT 1 OFFSET 1 for the 2nd rank and then LIMIT 1 and OFFSET 2 for the third rank! Thanks for all the help everyone!


  2. 0

    There exist one, two or three group categories of type ‘item’. You want to order them by priority_level. For each of these group categories you want to select one of its group names and call them first_category, second_category and third_category according to their priority_level rank.

    You can do this with the window function DENSE_RANK:

    WITH
  ranked AS
  (
    SELECT
      g.name, 
      DENSE_RANK() OVER (ORDER BY gc.priority_level) AS rn
    FROM groups g             
    INNER JOIN group_categories gc ON gc.id = g.group_category_id             
    WHERE gc.type = 'item'             
  )
SELECT
  ANY_VALUE(name) FILTER (WHERE rn = 1) AS first_category,
  ANY_VALUE(name) FILTER (WHERE rn = 2) AS second_category,
  ANY_VALUE(name) FILTER (WHERE rn = 3) AS third_category
FROM ranked;

    Here is the same query for more than one category type:

    WITH
  ranked AS
  (
    SELECT
      gc.type,
      g.name, 
      DENSE_RANK() OVER (PARTITION BY gc.type ORDER BY gc.priority_level) AS rn
    FROM groups g             
    INNER JOIN group_categories gc ON gc.id = g.group_category_id             
  )
SELECT
  type,
  ANY_VALUE(name) FILTER (WHERE rn = 1) AS first_category,
  ANY_VALUE(name) FILTER (WHERE rn = 2) AS second_category,
  ANY_VALUE(name) FILTER (WHERE rn = 3) AS third_category
FROM ranked
GROUP BY type
ORDER BY type;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search