sql server - SQL where clause for self-referencing table -

i have following table:

create table [dbo].[test] (     [id] [int] identity(1,1) not null,     [title] [nvarchar](450) null,     [description] [nvarchar](4000) null,     [created] [datetime] null,     [orgid] [int] null constraint [fk_test_orgid] foreign key references test(id),      constraint [pk_test]         primary key clustered ([id] asc) )  

a new entry has orgid = null. if entry has been edited, new row created orgid set original parent. if entry edited multiple times, children have orgid set id of original row. created datetime provides "order".

what need select newest versions.

given table below, looking select id 3, 5 & 6

id  title   description          created        preid ----------------------------------------------------- 1   car     orginal car          2014-01-01     null 2   house   original house       2014-01-01     null 3   bike    original bike        2014-01-01     null 4   car     car updated          2014-06-01     1 5   car     car updated again    2014-08-01     1 6   house   house updated        2014-09-01     2 

any input appreciated.

thanks.

since records pointing @ original row (and not previous one) :

select id, title, description, created, preid    (select id, title, description, created, preid,    row_number() over(partition isnull(orgid,id) order id desc) rn    test) rn = 1