SQL Server: How to select second-highest parentid?
I have a SQL Server database with these pages:
+------------+--------------+-------------------------------+ | pageid | parentid | title | +------------+--------------+-------------------------------+ | 1 | null | Home | +------------+--------------+-------------------------------+ | 2 | 1 | News | +------------+--------------+-------------------------------+ | 3 | 1 | User | +------------+--------------+-------------------------------+ | 4 | 3 | Edit profile | +------------+--------------+-------------------------------+ | 5 | 3 | Messages | +------------+--------------+-------------------------------+ | 6 | 5 | View all | 开发者_Go百科+------------+--------------+-------------------------------+
How do I select the second-highest (in level) parentid for any row? So for pageid=6 (View all) it should return parentid->3 (User).
For a fixed and known number of steps up the parent hierachy, use explicit joins:
select l2.*
from table t
join table l1 on t.parent_id = l1.pageid
join table l2 on l1.parent_id = l2.pageid
where t.pageid = 6;
For an unknow number of steps in the hierachy, use a recursive cte, but you need a stop criteria, see Recursive Queries Using Common Table Expressions.
Try:
select max(thing) from table where thing < (select max(thing) from table)
I couldn't pick from your question and your sample whether you want pageid or parentid.
精彩评论