• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

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

0
detox1978
Asked:
detox1978
  • 3
  • 3
2 Solutions
 
sunithnairCommented:
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

0
 
chapmandewCommented:
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]
) a
where row between 16 and 30
0
 
sunithnairCommented:
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

0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
detox1978Author Commented:
I get the following error;

Msg 8156, Level 16, State 1, Line 1
The column 'QuestionID' was specified multiple times for 'temptable'
0
 
detox1978Author Commented:
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

0
 
sunithnairCommented:
So you select it with the column names like this instead of *
select * from
(select column1, column2, column3, 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

0
 
detox1978Author Commented:
thanks
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now