Re: Help with Query

From: Roji. P. Thomas (lazydragon_at_nowhere.com)
Date: 03/08/04 

Date: Mon, 8 Mar 2004 20:55:39 +0530

>>1000393 -998999607
Why you named that as a FriendlyNum ;)? 

-- 
Roji. P. Thomas
SQL Server Programmer
"Mike" <anonymous@discussions.microsoft.com> wrote in message
news:7284AEAC-5350-49A6-9178-4443BE548A84@microsoft.com...
> 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
>

Relevant Pages

  • Help with Query
    ... I need a query that will do these things. ... The row that is returned will always have the higher ChildID ... All fields are int, with the exception of DEL_IND being tinyint. ... INSERT INTO Table1 VALUES ...
    (microsoft.public.sqlserver.programming)
  • Help with Query
    ... >ParentID ChildID FriendlyNum ... >All fields are int, with the exception of DEL_IND being ... >INSERT INTO Table1 ...
    (microsoft.public.sqlserver.programming)
  • Re: Can I put chunk of text from READTEXT into a variable ?
    ... One method is to use SUBSTRING to retrieve chunks of data and compare each ... CREATE TABLE Table1 ... Table1ID int NOT NULL ... CREATE PROC CompareTextColumns ...
    (microsoft.public.sqlserver.programming)
  • Best Approch??
    ... Cust_No INT, ... FROM table1 INNER JOIN ...
    (microsoft.public.sqlserver.programming)
  • Re: Multiple table insert
    ... id is unique in both Table1 and Table2 ... Table1ID int identity not null, ...
    (microsoft.public.sqlserver.programming)