How to relax GROUP BY restrictions in SQL Server?

Question

Consider this query:

SELECT F1,F2 FROM TABLE GROUP BY F1

Selecting F1 is valid, but F2 seems to be incorrect (after all it can change from row to row). However SQL Server does not check any logic involved here -- for example F2 could be dependent of F1 (because of the JOIN clause, for example).

I know the workarounds, but my question here is:

How to RELAX this "group by" restriction (directly)?

Something like:

RELAX_GROUPBY
SELECT F1,F2 ....

begin of edit 1

So it would be something similar to MySQL ability to get data without any workarounds from groupped dataset.

Example of data:

F1 | F2
1  | 2
1  | 2

Output (after executing the query given above):

F1 | F2
1  | 2

end of edit 1

Remark: yes, I do know the workarounds -- aggregate functions, creating view, table on-fly, and others (depending on scenario). I am not interested in another workaround. If you know the solution to the question please answer, thank you very much.

Answer 1

if you use group by you should use a Aggregate function then only it will work

e.g:

SELECT F1,count(F2) FROM TABLE GROUP BY F1

Answer 2

Assuming F2 is the same for every F1 (which is where your query is relevant), the easiest way is to do something like

SELECT F1, MAX(F2) AS F2
FROM TABLE
GROUP BY F1

assuming F2 is a field that can have aggregate functions applied to it, of course.

There's no way to relax the GROUP BY in the way you describe, short of rewriting the whole thing. I know MySQL does something a bit different (you can group by one field and SELECT all the others), but it's inconsistent with other implementations.

Answer 3

if you are so sure that F2 is dependent on F1, just add it to the group by (how difficult is that?):

SELECT F1,F2 FROM TABLE GROUP BY F1, F2

The "do what I mean and not what I code" portion of SQL Server will never be good enough to read your mind, tell it how to group the columns and it will do it. There is no facility within SQL Server to "relax" the group by restrictions, and I'm glad.