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
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'))
精彩评论