How do I write this SQL query?

Question

I have a table that records a history of child items linked to a parent, with two columns:

• ParentId
• ChildId

Some example data looks like this:

ParentId -- ChildId
001 -- 001
001 -- 001
001 -- 001
001 -- 002
001 -- 002
001 -- 002
001 -- 003
001 -- 003
001 -- 003
001 -- 003
001 -- 004
001 -- 004
001 -- 005
001 -- 005
001 -- 005
001 -- 005

I need to select all the rows with the highest value ChildId for a given ParentId. So in the above example, I need the query to return the following rows, given an input parameter of '@parentId = 001':

001 -- 005
001 -- 005
001 -- 005
001 -- 005

Thanks for any help!

Answer 1

select * 
from TABLENAME
where parentId = '001' and ChildID = (select MAX (ChildId))

Not sure if this will work, but give it a try.

Answer 2

How about this?

SELECT ParentID, MAX(ChildID) AS ChildID
FROM TableName
GROUP BY ParentID

Updated to edit missed requirement to return all rows:

Test Data

-- 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')

Results

-- 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

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.)

(SELECT MAX(childId) FROM #table WHERE parentId = @ParentID)

Answer 3

This aught to do it:

SELECT * FROM MyTable
WHERE parentId = '001'
AND childId = (SELECT MAX(childId) FROM MyTable WHERE parentId = '001')

Answer 4

To cater for multiple parents in the table, the following should help

select parentId, max(childid) as ChildId
from <table_name>
group by parentId

Answer 5

SELECT *
FROM TABLENAME
WHERE parentId = '001'
AND childid = (select MAX (ChildId) 
               from TABLENAME
               where parentId = '001')

Answer 6

SELECT *
FROM table_name
WHERE ParentId = @parentId
GROUP BY ParentId
HAVING ChildId = MAX(ChildId)