Where and top interaction in SQL Server view

Question

Do you know how to convince SQL Server to consider WHERE clauses inside a view? It seems that SQL Server only launches my view, evaluating TOP 1, and then applying the "where id = N" clause to filter the result set. Example:

create table mytable ( id int, name varchar(64) ); insert into mytable values ( 5, 'BOB' ); insert into mytable values ( 5, 'ROBERT' ); insert into mytable values ( 5, 'SMITH, ROBERT' ); insert into mytable values ( 8, 'AJ' ); insert into mytable values ( 8, 'Al J.' ); insert into mytable values ( 8, 'Albert Johnston' ); select * from mytable; +----+-----------------+ : id : name : +----+-----------------+ : 5 : BOB : : 5 : ROBERT : : 5 : SMITH, ROBERT : : 8 : AJ : : 8 : Al J. : : 8 : Albert Johnston : +----+-----------------+ create view myview as select top 1 id, name from mytable order by len(name) desc 

Problem:

 -- bad, empty result set -- (I want this to answer 5, 'SMITH, ROBERT') select * from myview where id = 5 

It works as expected:

 -- good, answers 8, 'Albert Johnston' select * from myview -- good, also answers 8, 'Albert Johnston' select * from myview where id = 8 

Now without a view:

 -- good, answers 5, 'SMITH, ROBERT' select top 1 id, name from mytable where id = 5 order by len(name) desc 

Answer 1

In this case, if you want to create something like this, I would suggest using something that returns a value to you using where, so it's better to create a Table-Valued User-Defined Function

 Create dbo.YourFunction ( @parameter id ) returns table as return select top 1 id, name from mytable where id = @parameter order by len(name) desc 

and use it like

 select * from dbo.yourFunction(5) 

Answer 2

Albert Johnston will always return your view, since it is impossible to get him to first evaluate the external condition. You need something like the following, give you the name / Id with the maximum length for each id group:

 select name, id from ( select name, id, row_number() OVER (partition by id order by len(name) desc) as [len] from theTable ) As tbl1 where [len] = 1