Friday, September 29, 2006

A Little Oedewaldt History

Recent contact from a distant Illinois Oedewaldt prompted me to google the Oedewaldt name for a little history. I found a wonderful glimpse of history in an electronic copy of "Historical Encyclopedia of Illinois and History of Peoria County" originally published in 1902.

OEDEWALDT, HENRY J.; Farmer and Miner; born in the city of Peoria, August 26, 1856. He is the son of Casper Oedewaldt, born in Prussia, October 25, 1822, died March 14, 1895; and Catherine Odenwelder, born in Baden, Germany, July 28, 1830, and still living; they were married in St. Louis, Missouri, about 1844. The senior Oedewaldt was a carriage maker and established the first carriage and wagon factory in Peoria. In 1857 he purchased the farm where his son, Henry J., now lives. Henry J. Oedewaldt married Mary Colvin at Maple Ridge, November 16, 1881. They have eight children: Sarah Catharine, born November 1, 1882; Cleveland, born March 4, 1885; Henry J., born April 14, 1888; Roger, born September 21, 1890; Snowden, born March 7, 1893; Seth, born June 2, 1895; Goldie, born July 28, 1898, and Adam, born February 10, 1900. Mrs. Oedewaldt's father, Benjamin Colvin, was born in Pennsylvania and died in Indian Territory, April 6, 1900, aged seventy years. He married Margaret Goodwill, who was born in Hollis Township in 1835, and is now living in the Indian Territory. Jacob, the only brother of H. J. Oedewaldt, was born in 1860. He married Flora Calhoun and has seven children. The Oedewaldt family are Lutherans. Henry J. is a Republican, and has served as School Director.

I can remember my grand-mother, married to Roger, referring to Henry, Snowden and Seth. I'm pretty sure I took a car ride across Pekin with Great uncle Snowden back in the summer of 1984. In fact, we may have been on our way to visit Great Aunt Goldie.

So, Casper immigrated from Prussia to Illinois [1840's?] who had Henry [1856] who had my Grandfather Roger [1890] who moved to Montana where my dad, Elliott [1937] was born.

Wow, it would be interesting to fill in some more of this history!

Friday, September 22, 2006

Delete Duplicates In One SQL Statement

Using a Microsoft T-SQL extension you are able to conduct a self-join on a DELETE statement. The hitch is you cannot use an alias for the first table in the second FROM clause. This works best if there is at least someway to uniquely idenify the table. This will not work with Updateable Views since they do not allow self-joins.

DELETE FROM tblBlah
FROM tblBlah
JOIN tblBlah a
ON tblBlah.ClientName = a.ClientName
AND tblBlah.ClientId > b.ClientId

For a more complete examination see my test code below. Just copy and paste into query anaylzer and give it a run.

SET NOCOUNT ON
-- Create Duplicate temporary table
IF Object_Id('tempdb.dbo.#dups') IS NOT NULL DROP TABLE #dups
CREATE TABLE #dups(
pk int identity(1,1),
Firstname varchar(50),
LastName varchar(50))-- Populate Duplicate table
Insert into #dups (FirstName, LastName)
Values ('Jack','Sparrow')
Insert into #dups (FirstName, LastName)
Values ('Jack','Sparrow')
Insert into #dups (FirstName, LastName)
Values ('Jack','Sparrow')
Insert into #dups (FirstName, LastName)
Values ('Will','Turner')
Insert into #dups (FirstName, LastName)
Values ('Will','Turner')
Insert into #dups (FirstName, LastName)
Values ('Elizabeth','Swann ')
Insert into #dups (FirstName, LastName)
Values ('Elizabeth','Swann ') -- The Table with all duplicates
SELECT * FROM #dups -- Delete duplicates and keep the min primary key record.
-- Reverse the comparison operator to keep the
-- max primary key record.
DELETE FROM #dups
FROM #dups
JOIN #dups b
ON #dups.FirstName = b.FirstName
AND #dups.LastName = b.LastName
AND #dups.pk > b.pk -- The table de-duped
SELECT *FROM #dups