title: Query optimization with rows referring to parents
body: <p>I have one table that has "Id", "ParentId", and "CreatedDate". If the row is an original submission, it will have no "ParentId". When an edit is made to an original submission, a new row is created where NewRow."ParentId" = Original."Id". Every new edit made from there on will take the proper "ParentId". This gives a way to see the history of edits. </p>
  
  <p>Now for the query. I rushed together a query that will get all of the latest and unique entries. For example. if I have 3 unique original forms. I only want to see their most recent revision (most recent child) unless they have none, in which case I want the original where "ParentId" IS NULL.</p>
  
  <p>This is the query I am using:</p>
  
  <pre><code>SELECT DISTINCT A.* 
  FROM   "dbo"."customercomplaint" AS A 
         RIGHT OUTER JOIN "dbo"."customercomplaint" AS B 
                       ON B."parentid" != A."id" 
  WHERE  A."parentid" IS NULL 
         AND A."id" IS NOT NULL 
  UNION 
  SELECT t1.* 
  FROM   "dbo"."customercomplaint" t1 
         JOIN (SELECT "parentid"         AS id, 
                      Max("createddate") AS "CreatedDate" 
               FROM   "dbo"."customercomplaint" 
               GROUP  BY id) t2 
           ON t1."parentid" = t2.id 
              AND t1."createddate" = t2."createddate" 
  </code></pre>
  
  <p>This query feels a little sloppy to me and I would like to seek out a better solution. Let me know if any further information is required. I appreciate any and all advice.</p>