[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

How to count rows within visual Studio 2005

I need to put a row number count to a report.  As long as the results of my query only come back with one row per IN I'm fine.  My problem comes into play when I have multliply lines of info for one item.  example  for a I.IN i could have more then one j.body brought back, when I look at the results I get skipped numbers because my row numbers are based on IN.  I have to do the count by the IN column.  How can I get my numbers not to skip when displaying the results.    I put a copy of my select below.  
SELECT
 row_number()  over(order by i.in) as row_number
I.IN, J.Body
FROM         I INNER JOIN J ON J.P_R = I.R
WHERE     (I.Createdate >= GETDATE() - 1) (I.CC <> 'xyz') ORDER BY I.CreateDate

Open in new window

Rownumberexample.xls
0
FLPeople
Asked:
FLPeople
1 Solution
 
appariCommented:
try this

SELECT
rowNum_I.row_number,
I.IN, J.Body
FROM       (Select  row_number()  over(order by in) as row_number, IN From
(Select distinct IN from I WHERE     (Createdate >= GETDATE() - 1) and (CC <> 'xyz') ) as dis_I ) rowNum_I Join
  I ON rowNum_I.P_R = I.R
INNER JOIN J ON J.P_R = I.R
WHERE     (I.Createdate >= GETDATE() - 1) (I.CC <> 'xyz') ORDER BY I.CreateDate
0
 
FLPeopleAuthor Commented:
I tried that, and I'm getting the error

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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