CODEWITHSUNDEEP
sqlsql-server
Thomas
Thomas

Reputation: 34188

SQL server wrong Row Number is showing

here is my sql

SELECT * FROM  
     (
  SELECT Row_number() OVER (ORDER BY [bbajobs].JID) AS RowNumber
       ,[BBAJobs].[JID]
       ,[AccountReference] as [Acc Ref]
       ,[BBAJobs].[OEReference] as [OERef]
       ,[JobType],[JobState]
       ,[JobShippedDate]
       ,[UPSShippingNumber]
       ,[CustomerName] [Customer Name]
       ,[ContactName] [Contact Name]
       ,[Telephone]
       ,[JobDescription]
       ,[CallRem].[rem]
       ,[CallRem].[callStatus]
       ,[CallRem].[ShopRemarks]
       ,CustomerNotes
       ,ShopNotes
       ,RecievedDate
       ,UserName 
   FROM dbo.BBAJobs LEFT OUTER JOIN dbo.CallRem 
ON dbo.BBAJobs.JID = dbo.CallRem.jid) x 
WHERE 1<2  
AND x.jid IN (SELECT CONVERT(VARCHAR, data) AS [JID] 
              FROM dbo.Split1('33180,33265,33047', ','))                  
AND RowNumber BETWEEN 1 AND 20

when i execute the above sql then rwo number is not showing like 1,2,3

when i exclude this line 

AND x.jid IN (SELECT CONVERT(VARCHAR, data) AS [JID] 
              FROM dbo.Split1('33180,33265,33047', ','))

then right row number is showing. without excluding that line & position can't the row number can be show right ? please help. thanks

Upvotes: 1

Views: 761

Answers (4)

M.Ali
M.Ali

Reputation: 69524

Try this ... filter out the rows inside you inner query 

SELECT * FROM  
     (
  SELECT Row_number() OVER (ORDER BY [bbajobs].JID) AS RowNumber
       ,[BBAJobs].[JID]
       ,[AccountReference] as [Acc Ref]
       ,[BBAJobs].[OEReference] as [OERef]
       ,[JobType],[JobState]
       ,[JobShippedDate]
       ,[UPSShippingNumber]
       ,[CustomerName] [Customer Name]
       ,[ContactName] [Contact Name]
       ,[Telephone]
       ,[JobDescription]
       ,[CallRem].[rem]
       ,[CallRem].[callStatus]
       ,[CallRem].[ShopRemarks]
       ,CustomerNotes
       ,ShopNotes
       ,RecievedDate
       ,UserName 
   FROM dbo.BBAJobs LEFT OUTER JOIN dbo.CallRem 
   ON dbo.BBAJobs.JID = dbo.CallRem.jid
   WHERE [BBAJobs].[JID] IN 
                          (SELECT CONVERT(VARCHAR, data) AS [JID] 
                           FROM dbo.Split1('33180,33265,33047', ',')) 
   ) x 
WHERE 1<2                   
AND RowNumber BETWEEN 1 AND 20

Upvotes: 0

user1948635
user1948635

Reputation: 1409

The row number is being calculated before the where clause on ID's is applied.

Move the where clause for the ID inside the bracketed statement, like this -

SELECT * FROM  
(
SELECT Row_number() OVER (ORDER BY [bbajobs].JID) AS RowNumber,                    
[BBAJobs].[JID],[AccountReference] as [Acc Ref],[BBAJobs].[OEReference] as [OERef],[JobType],[JobState],
[JobShippedDate],[UPSShippingNumber],[CustomerName] [Customer Name],[ContactName] [Contact Name],[Telephone],
[JobDescription],[CallRem].[rem],[CallRem].[callStatus],[CallRem].[ShopRemarks],
CustomerNotes,ShopNotes,RecievedDate,UserName 
FROM dbo.BBAJobs LEFT OUTER JOIN dbo.CallRem 
ON dbo.BBAJobs.JID = dbo.CallRem.jid
WHERE [bbajobs].jid IN (SELECT CONVERT(VARCHAR, data) AS [JID] FROM dbo.Split1('33180,33265,33047', ','))     
) 
x 
WHERE 1<2            
AND RowNumber BETWEEN 1 AND 20

Not tested, but it should work.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

If you want to select 20 rows from the subquery, then you need to make sure that any conditions that filter the rows are applied before/at the same time as you number the rows. Move the condition inside the subquery:

SELECT * 
FROM   (SELECT Row_number() 
                 OVER ( ORDER BY [bbajobs].jid ) AS RowNumber, 
               [bbajobs].[jid], 
               [accountreference]          AS [Acc Ref], 
               [bbajobs].[oereference]     AS [OERef], 
               [jobtype], 
               [jobstate], 
               [jobshippeddate], 
               [upsshippingnumber], 
               [customername]              [Customer Name], 
               [contactname]               [Contact Name], 
               [telephone], 
               [jobdescription], 
               [callrem].[rem], 
               [callrem].[callstatus], 
               [callrem].[shopremarks], 
               customernotes, 
               shopnotes, 
               recieveddate, 
               username 
        FROM   dbo.bbajobs 
               LEFT OUTER JOIN dbo.callrem 
                            ON dbo.bbajobs.jid = dbo.callrem.jid
        WHERE dbo.bbajobs.jid IN (SELECT CONVERT(VARCHAR, data) AS [JID] 
                     FROM   dbo.Split1('33180,33265,33047', ',')) ) x 
WHERE  1 < 2 
       AND rownumber BETWEEN 1 AND 20

Upvotes: 1

Dave C
Dave C

Reputation: 7392

The way this is written, the subquery 'x' is returning an ordered dataset. Once that dataset is returned, the following line is filtering it to rows that match it:

AND x.jid IN (SELECT CONVERT(VARCHAR, data) AS [JID] FROM dbo.Split1('33180,33265,33047', ','))

Moving this condition within the 'x' subquery should yield the results you want.

ie.

SELECT * FROM  
(SELECT Row_number() OVER (ORDER BY [bbajobs].JID) AS RowNumber,                    
[BBAJobs].[JID],[AccountReference] as [Acc Ref],[BBAJobs].[OEReference] as [OERef],[JobType],[JobState],
[JobShippedDate],[UPSShippingNumber],[CustomerName] [Customer Name],[ContactName] [Contact Name],[Telephone],
[JobDescription],[CallRem].[rem],[CallRem].[callStatus],[CallRem].[ShopRemarks],
CustomerNotes,ShopNotes,RecievedDate,UserName FROM dbo.BBAJobs LEFT OUTER JOIN dbo.CallRem 
ON dbo.BBAJobs.JID = dbo.CallRem.jid
AND [bbajobs].jid IN (SELECT CONVERT(VARCHAR, data) AS [JID] FROM dbo.Split1('33180,33265,33047', ','))) x 
WHERE 1<2   
AND RowNumber BETWEEN 1 AND 20

Upvotes: 1

Related Questions