I want to query for all products with the name "Shania Twain", but I want group them only by the tree with the deepest nlevel. Assuming I have a table like the following with the name categories So, for example, The problem lies with the HAVING clause requiring a boolean expression. The clause MAX(nlevel(cat.tree)) will return an integer. How do

Query to group by maximum depth of PostgreSQL Ltree?

I want to query for all products with the name “Shania Twain”, but I want group them only by the tree with the deepest nlevel.

Assuming I have a table like the following with the name categories

+---------------+---------------+---------------+
|id             |name           |tree           |
+---------------+---------------+---------------+
|1              |"Music"        |100            |
+---------------+---------------+---------------+
|2              |"Shania Twain" |100.1          |
+---------------+---------------+---------------+
|3              |"Artists"      |200            |
+---------------+---------------+---------------+
|5              |"Country"      |200.2          |
+---------------+---------------+---------------+
|6              |"Shania Twain" |200.2.4        |
+---------------+---------------+---------------+

So, for example,

SELECT MAX(cat.id),
       cat.name,
       MAX(cat.tree)
  FROM
       public.categories cat
 WHERE
       cat.name = "Shania Twain"
GROUP BY
       name
HAVING
       MAX(nlevel(cat.tree))

The problem lies with the HAVING clause requiring a boolean expression. The clause MAX(nlevel(cat.tree)) will return an integer.

How do I go about doing this?

Thanks in advance

Mahmoud

Answer

SELECT
    cat.id
    cat.name,
    cat.tree
FROM
    public.categories AS cat
WHERE
    cat.name = 'Shania Twain'
AND
   NLEVEL(cat.tree) = (SELECT MAX(NLEVEL(tree) FROM public.categories WHERE cat.name='Shania Twain'))

Advertisement

Answer