Is it possible to select node ancestors by SQL2 query?
For example
I have: /content/categories/sport/football node
Want to select: /content, /content/categories, /content/categories/sport nodes
You can, but assuming you have other siblings at those levels it's not very easy or dynamic. Honestly, it'll probably be far easier and far more performant to just use the Node methods to walk up the ancestors. Remember that you can get the Node object(s) for each row in a JCR-SQL2 query result.
Alternatively, if you just want the paths to the ancestors, then you can implicitly get these from the path of a result node (e.g., /content/categories/sport nodes).