Complicated 'Version-like' query

Question

Sorry for my title, it seems a little bit 'off' but couldn't find a better one explaining my problem.

So, I have a table like the following:

CREATE TABLE DocumentTable 
(
 id int identity primary key, 
 ParentId int, 
 CategoryId int,
 Version int
);

This is used for storing documents and keeping track of their version.

Is it possible to have an SQL that returns the latest Version documents? Or should I go and do this programmatically (that will lower performance by a lot, since I would select all Documents and then do the manipulations)

Here is also an SQLFiddle of my table with sample data.

For example I have this data in my Fiddle:

ID  PARENTID    CATEGORYID  VERSION 
1   (null)          1           1 
2   1               1           2 
3   2               1           3 
4   (null)          1           1 
5   (null)          1           1 
6   (null)          1           1 
7   4               1           2

The Id distinguishes the Document. The parentId shows witch document is the parent of it. So in my example I have 1 document with three version (1,2,3), 2 documents with no parent (5,6) and another document with 2 versions(4,7)

So for this data I want to select documents with id = 3,5,6,7

Answer 1

You can use NOT EXISTS

SELECT *
FROM   DocumentTable DT1
WHERE  NOT EXISTS(SELECT *
                  FROM   DocumentTable DT2
                  WHERE  DT2.ParentId = DT1.id) 

SQL Fiddle