eliminating duplicates

Question

Post reply

eliminating duplicates

Viewing 15 posts - 31 through 45 (of 137 total)

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

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 1

Wasn't TAB in Barry's also an intermediate table?

I could probably write it to use just one table.. but it'd be messy and got a day job..

karthik M One Orange Chip Points: 29139 More actions · Answer 2

rbarryyoung,

I have executed the part of your query. I am very surprised about your coding skill. Really, it is awesome code.

I just executed the below part only.

Select Name , Age, CHAR( (2*Count(*)) + (CASE Sex When 'M' Then 0 Else 1 End) )

From TAB Group By Name, Age, Sex

I didn't go further, becuase of some confusions and doubts in this part.

CHAR( (2*Count(*)) + (CASE Sex When 'M' Then 0 Else 1 End) )

Can you explain it for me ?

One more thing, I have changed 2 to 4,6,8..... The output returns different symbols for each number.

Can you explain it briefly ?

karthik

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 3

Using this test data:

INSERT INTO TAB

SELECT 'ABC',24,'M'

INSERT INTO TAB

SELECT 'ABC',24,'M'

INSERT INTO TAB

SELECT 'DEF',24,'M'

INSERT INTO TAB

SELECT 'DEF',24,'F'

INSERT INTO TAB

SELECT 'GHI',26,'F'

INSERT INTO TAB

SELECT 'GHI',26,'F'

INSERT INTO TAB

SELECT 'GHI',26,'F'

INSERT INTO TAB

SELECT 'GHI',26,'F'

INSERT INTO TAB

SELECT 'GHI',26,'F'

INSERT INTO TAB

SELECT 'LMN',27,'M'

INSERT INTO TAB

SELECT 'LMN',27,'M'

INSERT INTO TAB

SELECT 'LMN',27,'M'

INSERT INTO TAB

SELECT 'PQRS',25,'F'

INSERT INTO TAB

SELECT 'XYZ',24,'M'

INSERT INTO TAB

SELECT 'XYZ',25,'M'

These are my results:

ABC24M

GHI26F

LMN27M

These are Barry's

ABC24

ABC24M

GHI26

GHI26F

LMN27

LMN27M

Doesn't look right?? And I can't honestly say I understand his code.. I feel a bit dense ;/

goodguy SSCertifiable Points: 5456 More actions · Answer 4

[Quote]CHAR((2*Count(*)) + (CASE Sex When 'M' Then 0 Else 1 End)[/Quote]

Returns the ASCII character for the numeric value returned by double the count for MALE and double the count plus one for FEMALES.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

goodguy (6/26/2008)
[Quote]CHAR((2*Count(*)) + (CASE Sex When 'M' Then 0 Else 1 End)[/Quote]
Returns the ASCII character for the numeric value returned by double the count for MALE and double the count plus one for FEMALES.

Correct. Barry is being clever, in that he's using the "actual" table as the intermediate table (stretching the limits of the rules of engagement, but still very very slick). So - he's using one field to capture multiple pieces of information, so as to determine what needs to be deleted.

Janine - the only thing left (which Jeff and Barry pointed out earlier) is delete the encoded rows

delete from TAB where sex not in ('M','F')

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Sergiy SSC Guru Points: 110208 More actions · Answer 6

rbarryyoung (6/21/2008)
--remove the old leftover records:
Delete from TAB
Where NOT Sex IN('F','M')

Male/Female you say...

Look what professionals think about it:

http://www.nzhis.govt.nz/moh.nsf/pagesns/48/$File/4NMDSDataDictionaryv68.pdf

Go to page 118, chapter "Gender code".

See? 😛

_____________
Code for TallyGenerator

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

janine.rawnsley (6/26/2008)
Wasn't TAB in Barry's also an intermediate table?
I could probably write it to use just one table.. but it'd be messy and got a day job..

No... was meant to be the same table the data was stored in...

--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: 1004304 More actions · Answer 8

janine.rawnsley (6/26/2008)
Doesn't look right?? And I can't honestly say I understand his code.. I feel a bit dense ;/

Nah... Don't feel dense... Like I said above, Barry simply forgot to do the final cleanup step of removing his "archetype" rows.

To summarize what Barry's bit of computational heaven does... he creates new rows that contain the number of (rows-1)*2 as an ASCII byte... Multiplying by 2 forces all rows to be EVEN. Then, he adds one if it's an "F" row making only the "F" rows odd. Then, he deletes all rows except those new rows. Then he uses those new rows to recreate all of the old rows (minus 1) using a Tally table as the "loop". See the following article for what a Tally table is and how it can replace some loops...

http://www.sqlservercentral.com/articles/TSQL/62867/

The number of rows that he "reconstitutes" and whether they a "M" or "F" type rows are in that ASCII value we talked about in his "archetype" rows.

By the way... a loose definition of "archetype rows" means that the rows are very similar but different to the originals and, in this case, can be used to actually regenerate the originals.

--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)

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

I always hate these 'homework' questions 😉

But hats off to Barry - neat trick, though I'd never use it my databases, because I design them from the ground up not to be crap - allowing duplicates.:P

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

karthik M One Orange Chip Points: 29139 More actions · Answer 10

I never used this kind of coding in my queries.

Jeff,

If you explain a little bit more on rbarry's logic,it would be helpful for me.

CHAR((2*Count(*)) + (CASE Sex When 'M' Then 0 Else 1 End)

I am not clear in this place. If i change it to 3,4,5,6...etc it is showing different symbol for each number.

will changing the number affect the logic of this code ?

karthik

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

Don't look at the symbols... the symbols mean nothing to humans. What is important is the ASCII value... it can be 0 to 255 in a single character VARCHAR column. Barry is using it to store both the COUNT of the rows, and the Gender of the row. In order to do this, he stores the count as COUNT*2... that means that all counts result in an even number. Then, he adds 1 if the row is "F" gender.

When he goes to take things apart to rebuild the rows, he first looks at gender... if the COUNT value is odd, he uses "F" for the gender... "M" if other wise. Then, he divides the even portion of the count by 2 to get the actual count he needs.

--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: 1004304 More actions · Answer 12

Heh... Hey, Barry! Did I 'splain all that correctly? :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 13

Jeff Moden (6/27/2008)
Heh... Hey, Barry! Did I 'splain all that correctly? :hehe:

Pretty much, jeff. I haven't been jumping in because work has been pressing the last few days and I felt that any reply I gave would take some time.

I will try to write a blow-by-blow textbook document for it tonight. Justifieable, I think because it's full of stuff that us veterans take for granted (like how ASCII encoding works, etc.) that really should be spelled out for anyone who is interested.

[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: 1004304 More actions · Answer 14

If you're going to spend that much time on it... write it as an article and submit it, instead. 😉

As a side bar... you said something very important... you said "it's full of stuff that us veterans take for granted (like how ASCII encoding works, etc.) "...

People really get ticked at me when I say a lot of Developers and even people with CS degrees have no idea about computers... but they don't know ASCII ecoding, they don't know any binary math or how to do bit masking, they don't know how to convert from one numbering system to another, etc, etc. In other words, they've forgotten the bloody basics of computers themselves or maybe even never learned about them to begin with... and it shows in the quality of their code even if they don't have to use any ASCII encoding that day. 😉

--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)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 15

From Jeff Moden's post:

you're going to spend that much time on it... write it as an article and submit it, instead.
...
People really get ticked at me when I say a lot of Developers and even people with CS degrees have no idea about computers... but they don't know ASCII ecoding, they don't know any binary math or how to do bit masking, they don't know how to convert from one numbering system to another, etc, etc. In other words, they've forgotten the bloody basics of computers themselves or maybe even never learned about them to begin with... and it shows in the quality of their code even if they don't have to use any ASCII encoding that day

All I can add to Jeff's comment is AMEN brother AMEN. The current crop of graduates seems not to have been taught the basic fundamentals of computers and to some extent I blame it on Microsoft and the new languages such as dot net where the real guts are hidden from the user in predefined objects, but Microsoft can brag that that writing code is easier and faster, but does not ever state that the user's problem solving abilities are diminished.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]