Need to add where rownum between 0 and 0 + 6 -1 in a query
-
select ROW_NUMBER() OVER (ORDER BY pid DESC) as RowNum, pid,grpname from (select distinct p.ParticipantId as pid,p.pname + ' (' + p.username + ' )' as grpname from TSParticipants as p inner join TSGroupParticipants as gr on p.ParticipantId=gr.ParticipantId where p.status=0) as grplist in the above query i have to add the the below where condition WHERE RowNum BETWEEN 0 AND 0 + 6 -1 where should i add this condition if add it its showing error: Msg 207, Level 16, State 1, Line 8 Invalid column name 'RowNum'. Msg 207, Level 16, State 1, Line 8 Invalid column name 'RowNum'. -------------------------------- select * from ( select *, row_number() over (unique_column_name order by asc) as rownum from emp ) as test where rownum between 0 and 0 + 6 -1 ----- this above query gives all the rows i need only unique rows
-
select ROW_NUMBER() OVER (ORDER BY pid DESC) as RowNum, pid,grpname from (select distinct p.ParticipantId as pid,p.pname + ' (' + p.username + ' )' as grpname from TSParticipants as p inner join TSGroupParticipants as gr on p.ParticipantId=gr.ParticipantId where p.status=0) as grplist in the above query i have to add the the below where condition WHERE RowNum BETWEEN 0 AND 0 + 6 -1 where should i add this condition if add it its showing error: Msg 207, Level 16, State 1, Line 8 Invalid column name 'RowNum'. Msg 207, Level 16, State 1, Line 8 Invalid column name 'RowNum'. -------------------------------- select * from ( select *, row_number() over (unique_column_name order by asc) as rownum from emp ) as test where rownum between 0 and 0 + 6 -1 ----- this above query gives all the rows i need only unique rows
I have created a sample example. Hope this may help you. declare @tbl table(firstname varchar(50),lastname varchar(50)) insert into @tbl select 'firstname' + CONVERT(varchar(50),column_id) ,'lastname' + CONVERT(varchar(50),column_id) from sys.columns select * from @tbl
select firstname,lastname from
(select ROW_NUMBER() over(order by firstname) as rn,firstname,lastname
from
(
select
distinct(firstname),lastname
from @tbl t
)X(firstname,lastname))Y(rn,firstname,lastname)
where rn between 0 and 0 + 6 -1As per the understanding I made after going thru ur statement, I thought that u r looking for unique records. Henceforth, in the very beginning only I have used a distinct clause. Note:- I tried to explain the concept. You need to customise and implement as per ur requirement. Let me know in case of any concern. I cannot make out why instead of using 5 directly u are using 0+6-1? :)
Niladri Biswas