Query return different results in Query Analyzer

[coolbeachguy]

Hi,

I executed this query:

SELECT * FROM Orders where LocationID = 21 ORDER BY OrderNumber DESC

It returns 22 records.

Then I executed this query:

SELECT * FROM Orders where LocationID = 21 AND OrderNumber = 8145 ORDER BY OrderNumber DESC

The second query returns record, but this record is not returned by the first query. And it should be.

Both queries are executed in Query Analyzer.

I tried to execute both queries from Enterprise Manager, and both return correct results. So only incorrect in the Query Analyzer.

Any input is appreciated.

Thanks,

coolbeachguy

 

[vongrunt]

<blind guessing>
SET ROWCOUNT in action?
</blind guessing>

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

<more blind guessing>
Could it be that QA and Enterprise Manager used different indexes to get the data, and one of the indexes has gone wacky. Try rebuilding the indexes and then re-runt he query.
</more blind guessing>

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Are you connected to the 'real' db or maybe to a staging development or UAT DB from query analyzer, trust me happens all the time?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Hm... I've never heard of something like

INDEXPROPERTY(tableID, index, 'GoNutsInQueryAnalyzer')



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[coolbeachguy]

Not working guys.

I have never seen something like this either, it's weird

 

[vongrunt]

OK, simple test:

SELECT COUNT(*) FROM Orders where LocationID = 21
SELECT COUNT(*) FROM Orders where LocationID = 21 and OrderNumber = 8145
SELECT COUNT(*) FROM Orders where LocationID = 21 and OrderNumber <> 8145

Results are... ?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[awaywifye]

Also run

SELECT * FROM Orders WHERE OrderNumber = 8145

How many LocationID 21's are there....

 

[coolbeachguy]

ok:

SELECT COUNT(*) FROM Orders where LocationID = 21

returns 885 records, on both QA and Enterprise Manager.

SELECT COUNT(*) FROM Orders where LocationID = 21 and OrderNumber = 8145

returns 3 records, on both QA and Enterprise Manager.

SELECT COUNT(*) FROM Orders where LocationID = 21 and OrderNumber <> 8145

returns 882 records, on both QA and Enterprise Manager.

SELECT * FROM Orders WHERE OrderNumber = 8145

return 3 records

So all of those queries return correct results.

The one that does not work is if I do the ORDER BY OrderNumber DESC, in QA it does not return the correct results, in Enterprise Manager it does.

However, this is odd: when I do the ORDER BY OrderNumber ASC, it return correct results in both QA and Enterprise Manager.