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
LVL 2
FLPeopleAsked:
Who is Participating?
 
appariConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.