Hi Everyone
Based on the other responses, whilst I do not the ANSI rules documentation, this is what I have been able to verify from at least 2 database applications in the last couple of days viz SQL Server 2008 R2 and Oracle 11g :-
If you have all the variable that appear in the Select list also appearing in the Group by list without any variable in the Select list being associated with any summary function, you will definitely remove the duplicates - there is no question about it. If my memory serves me right - this goes back a few years - this was also how the SQL worked in DB2 database application.
And this is exactly what I am trying to find out - if SAS SQL is different in its implementation to the ANSI SQL implementation because as I have said earlier in each of the above database application a query like so :-
Select Region, MonthlyDate, CountByDayType
From Table1
Group by Region, MonthlyDate, CountByDayType
will remove duplicates by default and in Proc SQl on local datasets, it won't unless accompanied by Distinct keyword.
I am not looking for a solution to any problem, I am only looking for a confirmation if the implemenation of SAS SQL is different to ANSI SQL.
P.S.
I got this emall from someone last night in response but now it says that the post I am looking for has been permanently deleted. Here is the text from that email :-
Quote
In most DB SQL implementations, if you have a GROUP BY, all variables listed in the select clause must be included in the GROUP BY or be part of a summary function.
This is not the case in SAS. If you include other variables that are not in the GROUP BY statement then you will not have distinct values, since the other variable will be also selected. This can be a useful feature but can also be unexpected if you're using to working with DB implementations of SQL.
Your particular query doesn't include any summary functions so I'm not sure what the GROUP BY is intended to achieve in this particular case. It seems like a implicit way to specify distinct?
Unquote
Whosoever sent me this, please be it known that I agree with that too. I also understand how SAS SQL works with 'remerging back with the original dataset' when variables in select list do not all appear in the group by listng - no problem. I am fully aware that my query doesn't include any summary function and that is by intent to specifically determine if SAS and ANSI SQL (in some of the more common database applications) work differently!
Best regards
Deepak
... View more