Help with Query

From: PaulaC (anonymous_at_discussions.microsoft.com)
Date: 03/08/04 

Date: Mon, 8 Mar 2004 07:25:08 -0800

Try This...

select distinct * from Table1 where DEL_IND <> 1 and
ChildID = (select max(tab1.ChildID) from Table1 tab1 where
tab1.FriendlyNum = Table1.FriendlyNum)

>-----Original Message-----
>I need help with a query. A subset of the data table is
listed below.
>
>ParentID ChildID FriendlyNum
SubID DEL_IND
>3 3 1000393 -998999607 0
>3 3 1000395 -998999605 0
>3 4 1000395 -998999205 0
>3 3 1000776 -998999224 0
>3 4 1000776 -998999204 1
>
>I need a query that will do these things.
> Return only one row out of a set of rows with the
same FriendlyNum.
> The row that is returned will always have the higher
ChildID
> If a set of rows has the same FriendlyNum, and the
higher ChildID row
> has a DEL_IND (Delete Indicator) of 1, niether
of the set of rows should be
> returned.
>
>All fields are int, with the exception of DEL_IND being
tinyint. I have tried joining the Table into itself.
>
>Below is some code to create the table yourself.
>~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
>DROP TABLE dbo.Table1
>
>GO
>
>CREATE TABLE dbo.Table1
> (ParentID int,
> ChildID int,
> FriendlyNum int,
> SubID int,
> DEL_IND tinyint)
>
>INSERT INTO Table1
(ParentID,ChildID,FriendlyNum,SubID,DEL_IND) VALUES
(3,3,1000393,-998999607,0)
>INSERT INTO Table1
(ParentID,ChildID,FriendlyNum,SubID,DEL_IND) VALUES
(3,3,1000395,-998999605,0)
>INSERT INTO Table1
(ParentID,ChildID,FriendlyNum,SubID,DEL_IND) VALUES
(3,4,1000395,-998999205,0)
>INSERT INTO Table1
(ParentID,ChildID,FriendlyNum,SubID,DEL_IND) VALUES
(3,3,1000776,-998999224,0)
>INSERT INTO Table1
(ParentID,ChildID,FriendlyNum,SubID,DEL_IND) VALUES
(3,4,1000776,-998999204,1)
>SELECT * FROM TABLE1
>~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
>
>TIA, Mike
>
>.
>