Get youngest child

7 Jul 2005 3:39 PM

Suresh

Hi,
I am actually trying to find the youngest child in the site using a SET
based approach. I have attached some sample data and the result I am looking
for.
Thanks in Advance.
Suresh

IF OBJECT_ID('Site') IS NOT NULL
DROP TABLE dbo.Site
GO
CREATE TABLE dbo.Site ( SiteID INT NOT NULL
            ,Site VARCHAR(255) NOT NULL
            )
GO
INSERT INTO dbo.Site (SiteID, Site) VALUES (1, 'a.com')
INSERT INTO dbo.Site (SiteID, Site) VALUES (2, 'b.a.com')
INSERT INTO dbo.Site (SiteID, Site) VALUES (3, 'r.com')
INSERT INTO dbo.Site (SiteID, Site) VALUES (4, 'x.com')
INSERT INTO dbo.Site (SiteID, Site) VALUES (5, 'y.x.com')
INSERT INTO dbo.Site (SiteID, Site) VALUES (6, 'z.y.x.com')

GO
SELECT *
FROM    site s1

-- Here is the result I am expecting
-- 1 b.a.com
-- 3 r.com
-- 6 z.y.x.com

7 Jul 2005 4:46 PM

Jeremy Williams

Here is a hack I threw together. It should get you the answer I think you
are looking for, but there are most likely much more elegant ways of doing
this:

SELECT S1.SiteID, S1.Site FROM Site S1 INNER JOIN
(SELECT S3.SiteID, COUNT(*) AS NumOfOccurances
FROM Site S2 INNER JOIN Site S3 ON S2.Site LIKE '%' + S3.Site
GROUP BY S3.SiteID
HAVING COUNT(*) = 1) AS S4
ON S1.SiteID = S4.SiteID

It works on the idea that, if you join the table to itself using LIKE, any
Site name contained in another Site name will appear multiple times in the
join, so you exclude those Sites.

Also, as a personal preference, I would not use Site as both a column name
and a table name. It is a little confusing.

Show quote

"Suresh" <Sur***@discussions.microsoft.com> wrote in message
news:6F0E0BAE-A91A-4C59-A61F-914018701779@microsoft.com...
> Hi,
> I am actually trying to find the youngest child in the site using a SET
> based approach. I have attached some sample data and the result I am
looking
> for.
> Thanks in Advance.
> Suresh
>
>
>
> IF OBJECT_ID('Site') IS NOT NULL
> DROP TABLE dbo.Site
> GO
> CREATE TABLE dbo.Site ( SiteID INT NOT NULL
> ,Site VARCHAR(255) NOT NULL
> )
> GO
> INSERT INTO dbo.Site (SiteID, Site) VALUES (1, 'a.com')
> INSERT INTO dbo.Site (SiteID, Site) VALUES (2, 'b.a.com')
> INSERT INTO dbo.Site (SiteID, Site) VALUES (3, 'r.com')
> INSERT INTO dbo.Site (SiteID, Site) VALUES (4, 'x.com')
> INSERT INTO dbo.Site (SiteID, Site) VALUES (5, 'y.x.com')
> INSERT INTO dbo.Site (SiteID, Site) VALUES (6, 'z.y.x.com')
>
> GO
> SELECT *
> FROM site s1
>
> -- Here is the result I am expecting
> -- 1 b.a.com
> -- 3 r.com
> -- 6 z.y.x.com

8 Jul 2005 4:39 PM

Suresh

Thanks Jeremy, The solution worked well.

Thanks Thomas and Joe Celko.
I appreciate all of your help.

Show quote

"Jeremy Williams" wrote:

> Here is a hack I threw together. It should get you the answer I think you
> are looking for, but there are most likely much more elegant ways of doing
> this:
>
> SELECT S1.SiteID, S1.Site FROM Site S1 INNER JOIN
> (SELECT S3.SiteID, COUNT(*) AS NumOfOccurances
> FROM Site S2 INNER JOIN Site S3 ON S2.Site LIKE '%' + S3.Site
> GROUP BY S3.SiteID
> HAVING COUNT(*) = 1) AS S4
> ON S1.SiteID = S4.SiteID
>
> It works on the idea that, if you join the table to itself using LIKE, any
> Site name contained in another Site name will appear multiple times in the
> join, so you exclude those Sites.
>
> Also, as a personal preference, I would not use Site as both a column name
> and a table name. It is a little confusing.
>
> "Suresh" <Sur***@discussions.microsoft.com> wrote in message
> news:6F0E0BAE-A91A-4C59-A61F-914018701779@microsoft.com...
> > Hi,
> > I am actually trying to find the youngest child in the site using a SET
> > based approach. I have attached some sample data and the result I am
> looking
> > for.
> > Thanks in Advance.
> > Suresh
> >
> >
> >
> > IF OBJECT_ID('Site') IS NOT NULL
> > DROP TABLE dbo.Site
> > GO
> > CREATE TABLE dbo.Site ( SiteID INT NOT NULL
> > ,Site VARCHAR(255) NOT NULL
> > )
> > GO
> > INSERT INTO dbo.Site (SiteID, Site) VALUES (1, 'a.com')
> > INSERT INTO dbo.Site (SiteID, Site) VALUES (2, 'b.a.com')
> > INSERT INTO dbo.Site (SiteID, Site) VALUES (3, 'r.com')
> > INSERT INTO dbo.Site (SiteID, Site) VALUES (4, 'x.com')
> > INSERT INTO dbo.Site (SiteID, Site) VALUES (5, 'y.x.com')
> > INSERT INTO dbo.Site (SiteID, Site) VALUES (6, 'z.y.x.com')
> >
> > GO
> > SELECT *
> > FROM site s1
> >
> > -- Here is the result I am expecting
> > -- 1 b.a.com
> > -- 3 r.com
> > -- 6 z.y.x.com
>
>
>

7 Jul 2005 5:18 PM

Thomas Coleman

This is a prime example of weak design making queries significantly more
difficult. Assuming that "sites" are really Internet domains, you should break
up the domain name into it's constituent pieces like so:

Create Table Domains
(
    Address VarChar(255) Not Null Primary Key
    , Name VarChar(255) Not Null
    , TLD VarChar(5) Not Null
    , DateStamp DateTime
)

Insert dbo.Domains(Address, Domain, TLD) Values('a.com', 'a', 'com', '20050701')
Insert dbo.Domains(Address, Domain, TLD) Values('b.a.com', 'a', 'com',
'20050702')
Insert dbo.Domains(Address, Domain, TLD) Values('x.com', 'x', 'com', '20050703')
Insert dbo.Domains(Address, Domain, TLD) Values('y.x.com', 'x', 'com',
'20050702')
Insert dbo.Domains(Address, Domain, TLD) Values('z.y.x.com', 'x', 'com',
'20050701')

Now querying for the youngest become significantly simpler more straightforward:

Select D.Address, D.Domain, D.TLD
From Domains As D
Where Address = (
                            Select Min(D1.DateStamp)
                            From Domains As D1
                            Where D.Name = D1.Name
                                And D.TLD = D1.TLD
                            )

Thomas

Show quote

"Suresh" <Sur***@discussions.microsoft.com> wrote in message
news:6F0E0BAE-A91A-4C59-A61F-914018701779@microsoft.com...
> Hi,
> I am actually trying to find the youngest child in the site using a SET
> based approach. I have attached some sample data and the result I am looking
> for.
> Thanks in Advance.
> Suresh
>
>
>
> IF OBJECT_ID('Site') IS NOT NULL
> DROP TABLE dbo.Site
> GO
> CREATE TABLE dbo.Site ( SiteID INT NOT NULL
> ,Site VARCHAR(255) NOT NULL
> )
> GO
> INSERT INTO dbo.Site (SiteID, Site) VALUES (1, 'a.com')
> INSERT INTO dbo.Site (SiteID, Site) VALUES (2, 'b.a.com')
> INSERT INTO dbo.Site (SiteID, Site) VALUES (3, 'r.com')
> INSERT INTO dbo.Site (SiteID, Site) VALUES (4, 'x.com')
> INSERT INTO dbo.Site (SiteID, Site) VALUES (5, 'y.x.com')
> INSERT INTO dbo.Site (SiteID, Site) VALUES (6, 'z.y.x.com')
>
> GO
> SELECT *
> FROM site s1
>
> -- Here is the result I am expecting
> -- 1 b.a.com
> -- 3 r.com
> -- 6 z.y.x.com

7 Jul 2005 6:54 PM

--CELKO--

You have no key in the Sites table. You have no temproal columns, so
"youngest" makes no sense. If you wanted the path for a website
address, then store the leaf nodes and pull the parents from it in a
VIEW:

CREATE TABLE Websites
(website VARCHAR(255) NOT NULL PRIMARY KEY);
INSERT INTO Sites VALUES ('b.a.com');
INSERT INTO Sites VALUES ('r.com');
INSERT INTO Sites VALUES ('z.y.x.com');

Untested:

CREATE VIEW SitePaths (path)
AS
SELECT website
    FROM Websites
UNION
SELECT SUBSTRING (website, (S1.seq + 1), LEN(website))
    FROM Websites AS W, Sequence AS S1
WHERE S1.seq BETWEEN 2 AND LEN(website)
   AND SUBSTRING (website, S1.seg, S1.seq) = '.'
AND CHARINDEX (website, '.com') > 1;