INNER JOIN between two tables

What is the difference between these two expressions?

select max(tblRegistration.ID)ID,max(Text4)Text4, max(total)total FROM tblRegistration INNER JOIN AttendanceGrid ON tblRegistration.[ID]=AttendanceGrid.[ID]
Where  Committee = 'lacausa' and tblRegistration.Fiscal = 2010 and month = 'June' group by tblRegistration.ID order by Text4

select distinct tblRegistration.ID, text4, total FROM tblRegistration INNER JOIN AttendanceGrid ON tblRegistration.[ID]=AttendanceGrid.[ID]
Where  Committee = 'lacausa' and tblRegistration.Fiscal = 2010 and month = 'June'  order by Text4

I would think they produce the same results when using the Distinct clause vs the group by clause?
al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Normally there wouldn't be any, but in this case in the first expression...

>select max(tblRegistration.ID)ID,max(Text4)Text4, max(total)total
>group by tblRegistration.ID

What's the purpose of the GROUP BY line if all columns in the SELECT clause are aggregate values, meaning no column is grouped?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>select max(tblRegistration.ID)ID,max(Text4)Text4, max(total)total
Under normal circumsstances this would return a single row with the max of these three values.  Using GROUP BY would change that to however many tblRegistration.ID values there are as the number of rows, but without tblRegistration.ID in the SELECT I'm not sure how that would work.

>select distinct tblRegistration.ID, text4, total
This would return every unique combination of these three columns, with no Max-ing going on.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
al4629740Author Commented:
What does the Maxing do?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Max(any column name) gives you the maximum value of 'any column name'.

btw I wrote an article that may be a good read:  SQL Server Group By solutions.  If it helps you please hit the 'Yes' button at the bottom.

Thanks for the grade.  Good luck with your project.  -Jim
0
al4629740Author Commented:
Thank you
0
al4629740Author Commented:
btw, does maxing take more processing power from the server to complete?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Processing power, no, but it will take longer when you look at the query execution plan it involves a system aggregate.  What will come into play greater though will be the number of rows, whether they are character or numeric/date, and whether all columns involved are indexed.
0
al4629740Author Commented:
Shouldn't it automatically index?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Nope.   You have to explicitly state primary keys and indexes.
0
al4629740Author Commented:
I opened a new question that I want your input on
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.