August 26, 2008 at 1:37 am
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:
August 27, 2008 at 1:38 pm
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.
August 27, 2008 at 3:07 pm
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
ABC 24 M 2
LMN 27 M 3
LMN 27 M 3
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?
August 28, 2008 at 1:47 am
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:
August 28, 2008 at 8:12 am
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
January 7, 2009 at 4:14 am
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 🙂
January 7, 2009 at 5:01 am
windows_mss (1/7/2009)
IF object_id('tempdb..#tmpTBL') is not nullDROP 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
Change is inevitable... Change for the better is not.
January 7, 2009 at 7:16 am
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]
January 7, 2009 at 7:20 am
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:
January 7, 2009 at 7:46 pm
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
Change is inevitable... Change for the better is not.
January 7, 2009 at 7:49 pm
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
Change is inevitable... Change for the better is not.
January 7, 2009 at 8:03 pm
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]
January 7, 2009 at 8:15 pm
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
Change is inevitable... Change for the better is not.
January 7, 2009 at 8:47 pm
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.
January 7, 2009 at 10:57 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 91 through 105 (of 156 total)
You must be logged in to reply to this topic. Login to reply