asked on

TSQL: Display row 16 to 30

Hi All,

I have the TSQL query below. I count the rows using rownumber as 'row'.

My question is how can i only display row 16 to 30?

select *, row_number() over(order by A.[ID] desc) As row from
(select * from [MyDatabase].[dbo].[MyTable])  Q
join  [MyDatabase].[dbo].[MySecondTable]  A
on A.[best] ='5'
and A.[QuestionID] = Q.[QuestionID]

Open in new window

sunithnair

Try this one

select * from
(
select *, row_number() over(order by A.[ID] desc) As row from
(select * from [MyDatabase].[dbo].[MyTable])  Q
join  [MyDatabase].[dbo].[MySecondTable]  A
on A.[best] ='5'
and A.[QuestionID] = Q.[QuestionID]
) temptable where row>=16 and row<=30

Open in new window

SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sunithnair

or like this

select * from
(
select *, row_number() over(order by A.[ID] desc) As row from
(select * from [MyDatabase].[dbo].[MyTable])  Q
join  [MyDatabase].[dbo].[MySecondTable]  A
on A.[best] ='5'
and A.[QuestionID] = Q.[QuestionID]
) temptable where row between 16 and 30

Open in new window

detox1978

ASKER

I get the following error;

Msg 8156, Level 16, State 1, Line 1
The column 'QuestionID' was specified multiple times for 'temptable'

detox1978

ASKER

sorry typo in the code at the top..... i dont have a column called ID, it's QuestionID

select * from
(select *, row_number() over(order by A.[QuestionID] desc) As row from
(select * from[MyDatabase].[dbo].[MyTable] ) Q
join  [MyDatabase].[dbo].[MySecondTable]  A
on A.[best] ='5'
and A.[QuestionID] = Q.[QuestionID]
) a 
where row between 16 and 30

Open in new window

ASKER CERTIFIED SOLUTION

sunithnair

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

detox1978

ASKER

thanks