Solved

How do I return only the records that are between rowID 50 and 150 with this Select Statement

Posted on 2010-08-17
8
423 Views
Last Modified: 2012-05-10
I am trying to return only the 100 rows between the RowIDs of 50 and 150 from the following statement I get an error  of :

Msg 207, Level 16, State 1, Line 12
Invalid column name 'RowID'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'RowID'.
What is up with this?

SELECT
      ROW_NUMBER() over(order by [DBName],[D_ROW_ID] desc) RowID
      ,[DBName]
      ,[D_ROW_ID]
      ,[ASSETINDEX]
      ,[TRANSDATESTAMP]
      ,[TRANSTIMESTAMP]
      ,[FISCALYRADDED]
      ,[FAYEAR]
      ,[FAPERIOD]
  FROM [CUSTOM].[dbo].[test_TRANS]
  where RowID < 150 and RowID > 50
GO
0
Comment
Question by:Steve Samson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 4

Expert Comment

by:justin-clarke
ID: 33455772
Is your RowID column called D_ROW_ID ?

If so try...

WHERE ((D_ROW_ID < 150) AND (D_ROW_ID > 50))
0
 
LVL 18

Expert Comment

by:DarrenD
ID: 33455778
Hi,
Are you missing "as"

ROW_NUMBER() over(order by [DBName],[D_ROW_ID] desc) as RowID
0
 
LVL 3

Expert Comment

by:mnachu
ID: 33455798
One solution is to use CTE and get all rows into the CTE with the RowID as u have done.

After that use the Where clause and filter from the CTE.

Regards,
Nachi
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 33455799
You cannot use the derived column in the same Where It should rather be
SELECT * FROM
(
SELECT
      ROW_NUMBER() over(order by [DBName],[D_ROW_ID] desc) RowID
      ,[DBName]
      ,[D_ROW_ID]
      ,[ASSETINDEX]
      ,[TRANSDATESTAMP]
      ,[TRANSTIMESTAMP]
      ,[FISCALYRADDED]
      ,[FAYEAR]
      ,[FAPERIOD]
  FROM [CUSTOM].[dbo].[test_TRANS]
  )A
where RowID < 150 and RowID > 50
GO
0
 

Author Closing Comment

by:Steve Samson
ID: 33456648
THis worked flawlessly for what i was trying to accomplish
0
 
LVL 18

Expert Comment

by:DarrenD
ID: 33459207
Hi,

Just wondering. Would this work too. It's a single select so it could be faster! (if your selecting a lot of data). Two selects seems a little much for what you are trying to accomplish.

     SELECT
      ROW_NUMBER() over(order by [DBName],[D_ROW_ID] desc) RowID
      ,[DBName]
      ,[D_ROW_ID]
      ,[ASSETINDEX]
      ,[TRANSDATESTAMP]
      ,[TRANSTIMESTAMP]
      ,[FISCALYRADDED]
      ,[FAYEAR]
      ,[FAPERIOD]
  FROM [CUSTOM].[dbo].[test_TRANS]
where ROW_NUMBER() over(order by [DBName],[D_ROW_ID] desc) < 150 and ROW_NUMBER() over(order by [DBName],[D_ROW_ID] desc) > 50

Or...
Declare @rowID int
     SELECT
      @rowID  = ROW_NUMBER() over(order by [DBName],[D_ROW_ID] desc) RowID
      ,[DBName]
      ,[D_ROW_ID]
      ,[ASSETINDEX]
      ,[TRANSDATESTAMP]
      ,[TRANSTIMESTAMP]
      ,[FISCALYRADDED]
      ,[FAYEAR]
      ,[FAPERIOD]
  FROM [CUSTOM].[dbo].[test_TRANS]
where @rowID  < 150 and @rowID  > 50

Just an after thought....haven't tried it.

Darren
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33459303
Darren, the 1st SQL would sure work.. bu the 2nd SQL might end up giving an error as you cannot assign values with data retrieval oparations
0
 
LVL 18

Expert Comment

by:DarrenD
ID: 33459463
Nice one.
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question