Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1213141516»»

Just For Fun: An Impossible Delete Expand / Collapse
Author
Message
Posted Monday, November 2, 2009 8:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
Great article. I really liked the 'go back to the 70' part.
Sometimes when I see how stuff is done just because the computer can handle the load it drives me nuts.



---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #812395
Posted Monday, November 2, 2009 9:28 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:15 AM
Points: 5,471, Visits: 1,402
Great one....


Post #812440
Posted Monday, November 2, 2009 10:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, June 16, 2012 11:09 PM
Points: 202, Visits: 55
Thanks for your comments. The following modified solution works when you use the row_number() function to number the rows in an output query.

For the following query, the output for "row_number() over (order by name) as ident" will produce a column with the name of "ident" and a monotonically increasing integer value starting with 1. This is functionally the same as an identity column with a seed of 1!

select name, age, sex, row_number() over (order by name) as ident
from #temp

Here is the complete script:

create table #temp
(
name varchar(4),
age int,
sex varchar(1)
)

insert #temp
select 'ABC',24,'M'
union all
select 'ABC',24,'M'
union all
select 'LMN', 27, 'M'
union all
select 'LMN', 27, 'M'
union all
select 'LMN', 27, 'M'
union all
select 'PQRS', 25, 'F'
union all
select 'XYZ', 24, 'M'
union all
select 'XYZ', 25, 'M'

select a.name, a.age, a.sex, a.ident
from
(
select name, age, sex, row_number() over (order by name) as ident
from #temp
) a
inner join
(
select b.name, b.age, b.sex, min(b.ident) as ident
from
(
select name, age, sex, row_number() over (order by name) as ident
from #temp
) b
group by b.name, b.age, b.sex
having count(*) > 1
) c on a.name = c.name and a.age = c.age and a.sex = c.sex and a.ident > c.ident

drop table #temp

--***************************

The desired output that is obtained from executing this script is:

ABC 24 M 2
LMN 27 M 4
LMN 27 M 5


I hope that this helps.





Post #812485
Posted Monday, November 2, 2009 11:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, June 16, 2012 11:09 PM
Points: 202, Visits: 55
One final update to my solution. Using CTE's, the output query is much easier to understand.

Here is the complete script:

create table #temp
(
name varchar(4),
age int,
sex varchar(1)
)

insert #temp
select 'ABC',24,'M'
union all
select 'ABC',24,'M'
union all
select 'LMN', 27, 'M'
union all
select 'LMN', 27, 'M'
union all
select 'LMN', 27, 'M'
union all
select 'PQRS', 25, 'F'
union all
select 'XYZ', 24, 'M'
union all
select 'XYZ', 25, 'M';

with tmp
as
(
select name, age, sex, row_number() over (order by name) as ident
from #temp
)
select a.name, a.age, a.sex, a.ident
from tmp a inner join
(
select b.name, b.age, b.sex, min(b.ident) as ident
from tmp b
group by b.name, b.age, b.sex
having count(*) > 1
) c on a.name = c.name and a.age = c.age and a.sex = c.sex and a.ident > c.ident

drop table #temp

--***************************

The desired output that is obtained from executing this script is:

ABC 24 M 2
LMN 27 M 4
LMN 27 M 5



Post #812517
Posted Monday, November 2, 2009 6:09 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
hmi (10/30/2009)
Good brain training but are the restrictions as mentioned realistic? Which company would definitely not want to use a temp table or table variable?

Quite right, it's really just brain training.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #812680
Posted Monday, November 2, 2009 6:26 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
honza.mf (10/30/2009)
Phil Factor (8/5/2008)
Surely this is the simplest solution? (be warned of a nasty catch when creating these 'quirky updates'. the order of execution of the update is -variable assignments first then column assignments-both left to right!)



Very nice solution.
I Just prefer little change, it's more readable

--create the sample table
DECLARE @Sample TABLE (TheName VARCHAR(4),Identifier INT, sex VARCHAR(1))
INSERT INTO @Sample (TheName,Identifier,Sex)
SELECT 'ABC', 24, 'M' UNION ALL
SELECT 'ABC', 24,'M' UNION ALL
SELECT 'LMN', 27, 'M' UNION ALL
SELECT 'LMN', 27, 'M' UNION ALL
SELECT 'LMN', 27, 'M' UNION ALL
SELECT 'PQRS', 25, 'F' UNION ALL
SELECT 'XYZ', 24, 'M' UNION ALL
SELECT 'XYZ', 25, 'M'
DECLARE @hash VARCHAR(80), @sex VARCHAR(1)
UPDATE @sample
SET
@sex=CASE WHEN COALESCE(@hash,'')
<>TheName+CONVERT(VARCHAR(5),Identifier)+sex
THEN 'd' ELSE SEX END,
@hash= TheName+CONVERT(VARCHAR(5),Identifier)+sex,
Sex = @sex
DELETE FROM @sample WHERE sex='d'
SELECT * FROM @sample

The quirky update trick isn't really reliable without a Clustered index. For instance, when I execute you code, thi sis what I get:
TheName Identifier  sex
------- ----------- ----
ABC 24 M
LMN 27 M
LMN 27 M


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #812683
Posted Monday, November 2, 2009 6:29 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
Stephen.Richardson (10/30/2009)
...
-- Step 1 Get list of only DUPLICATE rows
INSERT INTO xSource (Name, Age, Sex)
SELECT '~'+Name, Age, Sex FROM xSource GROUP BY Name, Age, Sex Having COUNT(*) > 1

The problem is that this will fail if someone's name is already using all 50 characters (not unheard of). Otherwise I would have used this approach myself.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #812684
Posted Monday, November 2, 2009 6:30 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
skjoldtc (10/30/2009)
Thanks for the memories. A time when storage and memory was rare and expensive and you had to really know how the guts of the operating system, disk system, and memory allocation worked. Some of the old techniques are still useful today.

Great article and a great solution.

Thanks!


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #812685
Posted Monday, November 2, 2009 6:35 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
jghali (10/30/2009)
Funny how this article just came out today... I was actually on a similar thread last week and someone came up with a great solution...

My personal opinion the article is interesting but ... hmmm...

How about these solutions from this thread...
http://www.sqlservercentral.com/Forums/Topic793765-145-1.aspx

There's an undocumented identity key for every row of any table that can be used... in one simple delete statement you can delete the duplicate rows...

I was amazed...

Check it out.
Thanks

I am familiar with that thread (in fact I think that I am posted there), however, 1) it came out long after this article was originally published (15 months ago) and 2) those internal IDs are a lot hard to use than you might think, esp. given the restrictions. For instance, how would you propose using them to solve this problem?


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #812686
Posted Monday, November 2, 2009 6:40 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
arms.dan (10/30/2009)
Your off-hand comment about naming the field "Gender" rather than "Sex" piqued my curiosity. Why? As I understand the words, sex means biological differences: chromosomes and sexual organs. Gender refers to the characteristics a society or culture delineates as masculine or feminine. There's some blurring of the definitions, but I think they generally hold. So why do you prefer Gender for the column label?

In English "Sex" can also be a verb, thus leading to the joke about filling forms where is says "Sex[_]" with "Y". The PC way to stop this is to change the field name to "Gender[_]".


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #812688
« Prev Topic | Next Topic »

Add to briefcase «««1213141516»»

Permissions Expand / Collapse