Hi there,
To my knowledge, when there is a group by clause, the target list cannot include any (non-aggregated) non-group by columns:
Correct:
SELECT col1 FROM table group by col1; SELECT col1, sum(col2) FROM table group by col1;
Wrong: SELECT col1, col2 FROM table group by col1;
Some RDBMSs do allow this only when the group by columns (only col1 here) form a primary key. Then, you are sure you get only one value fore col2. Otherwise, the output of col2 would be undefined in the last query. I think some RDBMSs allow this even without the primary-key constraint, and just take the first (or any?) value for col2. But this kind of undefined behaviour is certainly not what you want from an RDBMS.
In MonetDB, I think the * resolves to col1 in your example because that's the content of the group by.
Best, Roberto
On 9 October 2014 16:20, Vijay Krishna vijayakrishna55@gmail.com wrote:
Hi,
The output of "*select * from table group by col1*" and "*select distinct(col1) from table*" appear to be *same*. But in SQL, group by returns all columns and distinct retrieves only that column values over which the function is ran.
Is there any way to retrieve non duplicate rows based on a single column (similar to group by in SQL) in MonetDB?
Thanks in advance.
With Regards,
Vijayakrishna.P. Mobile : 9500402305.
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list