Link to home
Start Free TrialLog in
Avatar of al4629740
al4629740Flag for United States of America

asked on

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?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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?
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of al4629740

ASKER

What does the Maxing do?
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
Thank you
btw, does maxing take more processing power from the server to complete?
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.
Shouldn't it automatically index?
Nope.   You have to explicitly state primary keys and indexes.
I opened a new question that I want your input on