Aggregating Data with MAX & GROUP BY in VIEWS

Hi EveryBody

Our Application generates a statement which invokes a view in a SQL-Server DB.
This statement selects DISTINCT Data-fields from the view
passing a restricted subset of IDs.

Background:

I am using SQL-Server 2008 R2 running on Windows Server 2008 R2
With an example-table I will try to explain the problem.

Given this example table: [TabA]
ID DATA


51 A1
50 A1
110 A5
100 A5


We then create the following view:

CREATE VIEW ViewOnTabA
AS
SELECT
MAX(ta.ID) As ID, ta.DATA
FROM
TabA ta
GROUP BY ta.DATA
GO


With this statement, generated by our application,
we call the view passing some ID values:

SELECT
ID, DATA
FROM ViewOnTabA
WHERE ID in (51,50,110,100)


The result is Ok
The Data fields are grouped and complete:

ID DATA


51 A1
110 A5


If instead of all IDs we pass only the smaller ID number: (50 instead of 51)
...
WHERE ID in (50,110,100)
The result is incomplete (ID 50 is missing):

ID DATA


110 A5

But we expected
ID DATA


50 A1
110 A5

It seems that the Aggregate->MAX in the view works whith the ID of the
whole [TabA] table and not whith the IDs of the calling statement(WHERE ID in ...).

Executing this Select in a normal query including
the (WHERE ID in ...) restriction it works fine:

SELECT
MAX(ta.ID) As ID, ta.DATA
FROM
TabA ta
WHERE ID in (50,110,100)
GROUP BY ta.DATA

output>>>>
ID DATA


50 A1
110 A5

For simulating this example
here are the create & insert statements:

create table TabA
(
ID int ,
DATA varchar(8)
)
go

insert into TabA values (51,'A1')
insert into TabA values (50,'A1')
insert into TabA values (110,'A5')
insert into TabA values (100,'A5')

Any help would be greatly appreciated.

Regards,
Alberto

This will never work because GROUP BY in view is executing first and then you are applying the WHERE.

1 Like

Thanks for the answer. I did not know this fact.
As we cannot invoke stored procedures from our application,
we have to rely on calling a view.
Are there other possibilities to have a distinct order
of the DATA fields (in the example) whithin a view.
The ID-fields must not necessarily be filtered with the MAX aggregate function.
But the Where restriction has to be applied on them.