asked
Aggregating Data with MAX & GROUP BY in VIEWS with a WHERE restriction
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
Do you have to use a view? Can you not use an inline table valued function? Something like this. Note you will need to get dbo.DelimitedSplit8K from [here][1] create table TabA (ID [INT], DATA [VARCHAR](8) ) insert into taba values (51,'A1'), (50,'A1'), (110,'A5'), (100,'A5') select * from taba go CREATE FUNCTION FuncOnTabA ( @ListOfIDs VARCHAR(MAX) ) RETURNS TABLE AS RETURN ( SELECT MAX(
ta.ID) As ID, ta.DATA FROM TabA ta WHERE
ta.ID IN (select item from dbo.DelimitedSplit8K(@ListOfIDs, ',')) GROUP BY ta.DATA ) GO select * from FuncOnTabA ('50,100,110') [1]:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Thank you for the answer. About your question regarding the use of VIEWs: Our interactive application generates SQL-statements based on a selection of "objects" done by users. The IDs of those objects are then used in the WHERE ID IN (...) clause. To retrieve the SQL-DB-Data, the application allows only an access through a TABLE or a VIEW Example: SELECT ID, DATA FROM ViewOnTabA WHERE ID in (50,110,100) OR SELECT ID, DATA FROM TabA WHERE ID in (50,110,100) We need to get the values of DATA (A1, A5 in the example) in a DISTINCT order, regardless which ID comes along with. So I attempted to use aggregate functions and GROUP BY in a VIEW to solve this request.
Do you need the ID column? Could you not just do SELECT DISTINCT data FROM TabA WHERE ID IN (50,110,100)
The code is generated automatically by the application and it is not changeable. The IDs have also to be retrieved, because they are used for further processing. I reduced the request to this plain and small example just to centre upon the essential problem.
Without being able to modify the code generated by the app, I am unsure how you are going to be able to do what you need. You need to pass in the where before the group by which a VIEW wont give you, so unless you can modify the query the app fires to do the aggregate direct on the table I can't think of an option, just yet SELECT max(id), data FROM TabA WHERE ID IN (50,110,100) GROUP BY data
18 People are following this question.
