Return the most frequent value

Question

Post reply

Return the most frequent value

Viewing 4 posts - 31 through 33 (of 33 total)

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi Jeff,

I was just thinking about us all returning different columns etc.

I've tested it with the same columns of AccNum, Id, Address and the results are very similiar.

Out of interest is mine not working?

You mentioned that some of the results were incorrect?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 2

Heh... that's what I was getting at... none of the methods has a huge advantage over the other which means it all becomes a personal preference for readability and understanding.

On the "incorrect returns"... Maybe I'm missing something... all of the result sets seem to have different addresses for the most frequent for many of the AccountNumbers. Could just be coffee deprevation on my part, though...:P

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Jeff,

When I run it the only difference I get is on the first one where there are no duplicate addresses, so depending on how you code it you get a different address. According to the OP (who I think we lost a long time ago) this was okay. The rest of the addresses matched.

I dumped the results for each method into a temp table with a person field to identify whose method it was from and then did a join and compare and they came out the same in all but the one I mentioned previously. Maybe I missed something. I actually closed my code, so I don't have it to post, but I basically made sure we all output the same fields and put each in the temp table and left joined on accountnum and person = "Jeff" each to the customers table.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

Thanks, Jack... must've been some latte in my coffee or sumthin':P

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