StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>How about this?</p> <pre><code>SELECT ParentID, MAX(ChildID) AS ChildID FROM TableName GROUP BY ParentID </code></pre> <p><strong>Updated to edit missed requirement to return all rows:</strong></p> <p><strong><em>Test Data</em></strong></p> <pre><code>-- Populate Test Data CREATE TABLE #table ( ParentID varchar(3) NOT NULL, ChildID varchar(3) NOT NULL ) INSERT INTO #table VALUES ('001','001') INSERT INTO #table VALUES ('001','001') INSERT INTO #table VALUES ('001','001') INSERT INTO #table VALUES ('001','002') INSERT INTO #table VALUES ('001','002') INSERT INTO #table VALUES ('001','002') INSERT INTO #table VALUES ('001','003') INSERT INTO #table VALUES ('001','003') INSERT INTO #table VALUES ('001','003') INSERT INTO #table VALUES ('001','003') INSERT INTO #table VALUES ('001','004') INSERT INTO #table VALUES ('001','004') INSERT INTO #table VALUES ('001','005') INSERT INTO #table VALUES ('001','005') INSERT INTO #table VALUES ('001','005') INSERT INTO #table VALUES ('001','005') </code></pre> <p><strong><em>Results</em></strong></p> <pre><code>-- Return Results DECLARE @ParentID varchar(8) SET @ParentID = '001' SELECT T1.ParentID, T1.ChildID FROM #table T1 JOIN ( SELECT Q1.ParentID, MAX(Q1.ChildID) AS ChildID FROM #table Q1 GROUP BY ParentID ) ParentChildMax ON ParentChildMax.ParentID = T1.ParentID AND ParentChildMax.ChildID = T1.ChildID WHERE T1.ParentID = @ParentID </code></pre> <p>Note: The performance of this solution is identical to the accepted solution (according to SQL Server profiler) using the following statement in the WHERE clause. But I like my solution better as it seems cleaner to me and can be easily extended to include other ParentIDs is required. (For example, reporting purposes.)</p> <pre><code>(SELECT MAX(childId) FROM #table WHERE parentId = @ParentID) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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