PROJECT
proj_id  orig_proj_id  proj_name

1        null          Project One
2        null          Project Two
3        1             Project One - B1
4        1             Project One - B2
5        2             Project Two - B1
6        1             Project One - B3

RESULT
proj_id  orig_proj_id  proj_name

5        2             Project Two - B1
6        1             Project One - B3

SELECT TOP 1 * FROM PROJECT WHERE ??? ORDER BY PROJECT.proj_name DESC

select t1.proj_id,t1.orig_proj_id,t1.proj_name
from PROJECT t1 inner join 
(
select max(b.proj_id) as proj_id,a.proj_name
from PROJECT a 
inner join PROJECT b on b.proj_name like a.proj_name + '%' and a.orig_proj_id is null
group by a.proj_name
) t2
on t1.proj_id = t2.proj_id
order by t1.proj_id

select proj_id,
orig_proj_id,
proj_name
from
(
row_number() over (partition by isnull(orig_proj_id,proj_id) order by proj_id desc) as seq,
proj_id,
orig_proj_id,
proj_name
from table
)t
where seq=1

quote:

---

Originally posted by shane1890

* No two projects will have the same name

---