question

Hi EveryBody Here is my Request. Our Application generates a statement which invokes a view in a SQL-Server DB. This statement selects DISTINCT or GROUP BY 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 [INT] DATA [VARCHAR(8)] --- ---- 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 in a VIEW the GROUP BY is executing before the WHERE condition. 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. 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