Just For Fun: An Impossible Delete

Question

Just For Fun: An Impossible Delete

Viewing 15 posts - 91 through 105 (of 156 total)

You must be logged in to reply to this topic. Login to reply

Shaun McGuile SSCarpal Tunnel Points: 4111 More actions · Answer 1

John I agree with your concerns and general comments - I was just making the point that duplicate elimination was not what was required. 🙂

Hiding under a desk from SSIS Implemenation Work :crazy:

Jasmine D. Adamson Hall of Fame Points: 3428 More actions · Answer 2

John R. Hanson (8/24/2008)
I am suggesting that one can use only manipulation of the extra bits available and not resort to adding additional rows to arrive at a solution.

That's what immediately came into my head as well. I've got some old-skool experience myself, and I see 7 wasted bits on every row - more than enough space to fulfill the requirement.

I liked the article because it shows the importance of managing your storage and memory usage. We used to run entire operating systems in 1K of space - how much does Vista eat before it even starts up? 500MB? A GB? I would bet a lot of that space is wasted. Modern programmers don't seem to take memory into account, as if it's infinite, and bloat-ware like Vista is what you get.

I disagree with your comment about "gender" though. Gender is not simply the new word for sex. Gender and sex are two different things - if this was a medical database, and what they really wanted to know is someone's physical status, then sex is correct. If this is a demographic database and someone's social identity is more important, then they would use gender. People whose sex is different from their gender are more common than you might think.

john.arnott SSChampion Points: 11882 More actions · Answer 3

Jasmine and John Hansen,

I'm with Jeff on this one: I'd like to see the specifics of solution that doesn't add any new rows at all. It's an attractive idea to say you can put the group count in the unused bits of the sex column, but then what? If you do that as a first step, your table would then logically look like this (with Count physically a part of the Sex column):

Name Age Sex Count

ABC 24 M 2

LMN 27 M 3

PQRS 25 F 1

XYZ 24 M 1

XYZ 25 M 1

So far so good. Now the trick is to delete the first of each group of identical rows. At that point, aren't we back to where we started? The original solution used the count to create one-less-than-count new rows that were distinguishable from the original and omitted that "first" row. That is, it inserted one row for ABC/24/M, two rows for LMN/27/M and none for the remaining singlets. Once that was done, the intermediate rows holding the counts and the original rows were all deleted.

By using the bits in the original rows, you can save the insertion of temporary rows to hold the counts, but I don't see how you get away from inserting new rows of the original format before deleting the altered rows with their counts.

So, what are we missing here?

Shaun McGuile SSCarpal Tunnel Points: 4111 More actions · Answer 4

Ah maybe the Sex column no - longer contains M or F for the subsequent identical rows

but M/F + 1 and M/F + 2 for second and third respectivly.

then delete all rows where 'sex' = M or 'sex' = F

and reset M/F + 1, M/F + 2 back to M/F afterwards 🙂

Am I making sense? 😉

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Andrew in WV SSCommitted Points: 1655 More actions · Answer 5

You are making sense, but I don't see how you'd do it. Any update query you run will update all the rows that have the same values for all three columns. I can't see any way that you could update all the rows for the duplicates while leaving one row for each distinct combination untouched. If you could, the same logic should apply for a delete query, and you should be able to delete the distinct rows without having to jump through the hoops to modify anything.

Andrew

--Andrew

windows_mss SSCrazy Points: 2423 More actions · Answer 6

IF object_id('tempdb..#tmpTBL') is not null

DROP TABLE #tmpTBL;

select distinct * into #tmpTBL from exampletable

truncate table exampletable

insert exampletable select * from #tmpTBL ORDER BY Name

drop table #tmpTBL

___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 7

windows_mss (1/7/2009)
IF object_id('tempdb..#tmpTBL') is not null
DROP TABLE #tmpTBL;
select distinct * into #tmpTBL from exampletable
truncate table exampletable
insert exampletable select * from #tmpTBL ORDER BY Name
drop table #tmpTBL

Yes... you and everyone else knows that... now, go back and look at ALL the requirements at the beginning of the article... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Heh. I'm telling you, there's got to be at least a graduate thesis in Psychology or Communication on this. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Shaun McGuile SSCarpal Tunnel Points: 4111 More actions · Answer 9

Someone failed to read the parameters.....like that'll ever happen. 😉

'You can educate some of the people some of the time, but not all of the people all of the time.'

--Shaun T'zu's Art of Education

Hiding under a desk from SSIS Implemenation Work :crazy:

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 10

RBarryYoung (1/7/2009)
Heh. I'm telling you, there's got to be at least a graduate thesis in Psychology or Communication on this. 🙂

What's really sad and spooky at the same time is that these people are "at large" and making design decisions every single day. The good thing about that is there's lots for some of us to clean up and get paid well. The bad part about it is that it costs the general public some pretty big "hidden" dollars to clean up the bad code that some of these folks leave behind because they didn't read and heed the requirements even after it's been explained that deviations are not and cannot be allowed for one reason or another.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 11

Shaun McGuile (1/7/2009)
Someone failed to read the parameters.....like that'll ever happen. 😉
'You can educate some of the people some of the time, but not all of the people all of the time.'
--Shaun T'zu's Art of Education

Man, I guess not... you should have seen the original post.... Like Barry said, there's at least one huge study on human behavior and technical prowess in there. Hell, you could spend a week just on the "don't press that button" syndrome! 😛 Most people don't read the danger sign and the bad ones did and pressed the button anyway... :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Personally, I think that it is some kind of psychological "blind spot" that makes them think that they know what this is before they read the whole description, and then they skip to the end and hit "reply".

Man, I really though that we would fix this when the article was published and we started this second thread...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 13

RBarryYoung (1/7/2009)
Personally, I think that it is some kind of psychological "blind spot" that makes them think that they know what this is before they read the whole description, and then they skip to the end and hit "reply".
Man, I really though that we would fix this when the article was published and we started this second thread...

Heh... what makes you think that there's a thinking process involved here? 😛 These are all just "quick kill" knee jerk reactions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

john.arnott SSChampion Points: 11882 More actions · Answer 14

Jeff and RBarry,

I have the highest regard for both of you. I've learned more than I would have thought possible from your insightful and thorough analyses and solutions. You've each demonstrated an amazing mastery of the field and brilliantly creative work that always seems upon reflection to be the only way to achieve the goal.

With that being said, I really must ask you to consider the effect the tone your recent comments would have on the newbie who posted a "solution" that didn't meet the original criteria. This may have been an opportunity to gently teach a lesson.

To "windows_mss",

It does take more than knowledge of the tools to reach one's full potential in any field. Learning to listen and understand the situation before acting will help no matter what your endeavor. I'd suggest that you not take the rather caustic comments personally, but try to understand that the habit of making sure the requirements for a task are clear is something to nurture whether at work or cruising this web site.

One of the teachers at my younger son's high school taught this very lesson by handing out an assignment with instructions that began "Read all of these instructions before starting", then went on with steps that involved drawing a figure, calculating a sum, finding a rhyme and so on. The final instruction was "Ignore all previous instructions except the first. Put your name on this paper and otherwise leave it blank." The teacher told me that there'd always be a number of students who were embarrassed by having jumped in without reading all the way through.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 15

Your point is very well taken and I'm sorry to disappoint. It's been a bit insane on my side of the house with this very problem of people just jumping to conclusions and I've allowed myself to get a bit carried away. Thanks for the prod.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)