"Prashanth" <someone@microsoft.com> wrote in message
news:uZpOlpUpDHA.1676@TK2MSFTNGP09.phx.gbl...
> create table tblTest
> (
> nId int Primary key identity(1,1) not null,
> sName varchar(50),
> nMarks numeric(5, 2)
> )
>
> Declare @name varchar(50)
> Declare @marks numeric(5, 2)
>
> Declare @count int
> set @count = 0
> while(@count < 100)
> Begin
> set @count = @count + 1
> insert tblTest values(cast(@count as varchar(12)) , rand() * 100)
> End
>
> select * from tblTest
>
> This will create a table with a list of random data.
> I want to display all the ranks between 5 and 8. along with their nId and
> nMarks.
>
>
> This is not all i have to use only 2 select statements.
>
> The query below returns me all the ranks , id and marks.
> Select 'Rank' = (select count(distinct nMarks) from tblTest B Where
> B.nMarks > A.nMarks) , nMarks, nId From tblTest as A
>
> but i am not able to execute
> Select 'Rank' = (select count(distinct nMarks) from tblTest B Where
> B.nMarks > A.nMarks) , nMarks, nId From tblTest as A
> Where Rank Between 5 and 8
>
> Fails. How do you perform an where operation on computed column.
>
> Thanx.
> PK
>
>

"Prashanth" <someone@microsoft.com> wrote in message
news:uZpOlpUpDHA.1676@TK2MSFTNGP09.phx.gbl...
> create table tblTest
> (
> nId int Primary key identity(1,1) not null,
> sName varchar(50),
> nMarks numeric(5, 2)
> )
>
> Declare @name varchar(50)
> Declare @marks numeric(5, 2)
>
> Declare @count int
> set @count = 0
> while(@count < 100)
> Begin
> set @count = @count + 1
> insert tblTest values(cast(@count as varchar(12)) , rand() * 100)
> End
>
> select * from tblTest
>
> This will create a table with a list of random data.
> I want to display all the ranks between 5 and 8. along with their nId and
> nMarks.
>
>
> This is not all i have to use only 2 select statements.
>
> The query below returns me all the ranks , id and marks.
> Select 'Rank' = (select count(distinct nMarks) from tblTest B Where
> B.nMarks > A.nMarks) , nMarks, nId From tblTest as A
>
> but i am not able to execute
> Select 'Rank' = (select count(distinct nMarks) from tblTest B Where
> B.nMarks > A.nMarks) , nMarks, nId From tblTest as A
> Where Rank Between 5 and 8
>
> Fails. How do you perform an where operation on computed column.
>
> Thanx.
> PK
>
>