CODEWITHSUNDEEP
sql
andyhasit
andyhasit

Reputation: 15289

TSQL similar query different results

This query:

SELECT ROWNUMBER,FATHERITEMNO,FATHERVARIANT
FROM STOCKBILLMAT WHERE FATHERVARIANT ='LOCK' 
AND FATHERITEMNO ='U5138'

gives me 2 results:

12779526   "U5138"   "LOCK"
12779527   "U5138"   "LOCK"

But the following query on the same table:

SELECT ROWNUMBER,FATHERITEMNO,FATHERVARIANT
FROM STOCKBILLMAT WHERE FATHERVARIANT ='LOCK' 
AND ROWNUMBER > 12818500 
AND ROWNUMBER < 12818590

gives me these 5:

12818532   "U5318"   "LOCK"
12818533   "U5318"   "LOCK"
12818558   "U5318"   "LOCK"
12818559   "U5318"   "LOCK"
12818560   "U5318"   "LOCK"

My problem is that unless i'm having a seriously blonde moment, all the rows returned by the second query "should" have been returned by the first query too!

Notes:

  1. I only added the ROWNUMBER range to narrow down the results because I knew roughly where they were. If I omit that filter and just scroll down, I see the 5 results, but not the 2 from the first query...
  2. STOCKBILLMAT is a table, not a view.
  3. The queries are executed on the same database, in the same session of QueryExpress, same everything.
  4. There are multiple databases on the server with roughly the same data, and the same thing happens on the others too.

Any idea why this might be?

Upvotes: 0

Views: 63

Answers (2)

Paul Williams
Paul Williams

Reputation: 17020

The queries are off by the row numbers. The first query returns the ROWNUMBERs 12*7*79526 and 12*7*79527, but the second one is a different range that begins with 12*8*.

Upvotes: 0

chue x
chue x

Reputation: 18803

Change your first query from U5138 TO U5318:

SELECT ROWNUMBER,FATHERITEMNO,FATHERVARIANT
FROM STOCKBILLMAT WHERE FATHERVARIANT ='LOCK' 
AND FATHERITEMNO ='U5318'

Upvotes: 2

Related Questions