Databases
StackOverflow2013
Postsdbo
POHow To Limit Self Join To Top 1 in SQL Server
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow To Limit Self Join To Top 1 in SQL Server
    primarykey
    data
    text
    <p>I need to perform a self join that can result in multiple rows, but I need to limit the join to a single row per record. When multiple rows match the join criteria, only the value row with the maximum PK should be used. Here is a simplified schema, hypothetical:</p> <pre><code>CREATE TABLE #Records( Id int NOT NULL, GroupId int NOT NULL, Node varchar(10) NOT NULL, Value varchar(10) NULL, Meta1 varchar(10) NULL, Meta2 varchar(10) NULL, Meta3 varchar(10) NULL ) </code></pre> <p>Here are some sample inserts:</p> <pre><code>INSERT INTO #Records VALUES(1,123,'Parent', '888', 'meta1', 'meta2', 'meta3') INSERT INTO #Records VALUES(2,123,'Guardian', '789', 'meta1', 'meta2', 'meta3') INSERT INTO #Records VALUES(3,123,'Parent', '999', 'meta1', 'meta2', 'meta3') INSERT INTO #Records VALUES(4,123,'Guardian', '654', 'meta1', 'meta2', 'meta3') INSERT INTO #Records VALUES(5,123,'Sibling', '222', 'meta1', 'meta2', 'meta3') INSERT INTO #Records VALUES(6,456,'Parent', '777', 'meta1', 'meta2', 'meta3') INSERT INTO #Records VALUES(7,456,'Guardian', '333', 'meta1', 'meta2', 'meta3') </code></pre> <p>In generic terms, I would want the count of rows returned to equal the number or records in the table. I need a Parent column in a Guardian column. Parent should equal the most recent row, based on Id, that has a Node of 'Parent', for the matching GroupId. I need the same for Guardian, but the Node should be 'Guardian'. Results would look like this: </p> <pre><code>Id GroupId Node Value Meta1 Meta2 Meta3 Parent Guardian --- ---------- --------- --------- ------- ------- ------- ------- ---------- 1 123 Parent 888 meta1 meta2 meta3 999 654 2 123 Guardian 654 meta1 meta2 meta3 999 654 3 123 Parent 999 meta1 meta2 meta3 999 654 4 123 Guardian 789 meta1 meta2 meta3 999 654 5 123 Sibling 222 meta1 meta2 meta3 999 654 6 456 Parent 777 meta1 meta2 meta3 777 333 7 456 Guardian 333 meta1 meta2 meta3 777 333 </code></pre> <p>Note, I have this partially working now, but it does not limit to the latest value. It works fine when all parent and guardian value nodes have the same value. I was attempting to limit to MAX, but have failed. Looking at this query may bias your judgement, so please don't hesitate to toss it out completely.</p> <pre><code>SELECT #Records.*, Parent,Guardian FROM #Records LEFT JOIN ( SELECT MAX(Id) As ParentRow, GroupId, Value AS Parent FROM #Records WHERE Node = 'Parent' GROUP BY GroupId, Value ) AS Parents ON #Records.GroupId = Parents.GroupId LEFT JOIN ( SELECT MAX(Id) As ParentRow, GroupId, Value AS Guardian FROM #Records WHERE Node = 'Guardian' GROUP BY GroupId, Value ) AS Guardians ON #Records.GroupId = Guardians.GroupId </code></pre> <p>Thanks in advance!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USkakridge
    1. USkakridge
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow To Limit Self Join To Top 1 in SQL Server
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow To Limit Self Join To Top 1 in SQL Server
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload