2 querys not returning the same data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

2 querys not returning the same data

Author

Topic

chabian
Starting Member

2 Posts

Posted - 2008-04-17 : 10:43:31

I have two databases with table structures exactly the same. The query in one database works and returns correctly. The same query (copied and pasted) in another database returns the correct records but completely ignores the order by. Even stranger, when you are creating the query under modify and run it, it is fine. But when I right-click on the query and go to 'Open View' then the query does not return correctly. Below is the query:

SELECT TOP (100) PERCENT pf.description AS incentive, CAST(i.targetc AS FLOAT) AS targetc, i.targetp, b.basis, d.description, i.iSelect, i.direction, pf.pValue,
pf.pFigure
FROM dbo.PerfFactorWeights AS pfw INNER JOIN
dbo.PerfFactors AS pf ON pfw.incentiveID = pf.incentiveID INNER JOIN
dbo.Incentive AS i ON pfw.incentiveID = i.incentiveID AND pfw.basis = i.basisID INNER JOIN
dbo.Basis AS b ON i.basisID = b.basisID INNER JOIN
dbo.Division AS d ON pfw.division = d.uniqueID
ORDER BY incentive, d.uniqueID, b.basis

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-17 : 11:06:45

Are the collation of both the databases same? Didnt understand second part. Is this query inside a view definition?

chabian
Starting Member

2 Posts

Posted - 2008-04-17 : 11:34:23

Yes, the collation of both databases are the same and it is in a view definition.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-17 : 11:36:40

Are you telling about the difference in results b/w running query directly and that obtained when querying from view?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-04-17 : 23:36:06

As of SQL Server 2005 the order by is now correctly disregarded in the view. You need to put ORDER BY on your select statement and remove it from the view.
FYI when you use ORDER BY in a view it is only used to calculate the TOP part. You will still need to order your final results as the rows returned are not guaranteed to still be in the order used for the TOP. TOP 100 PERCENT is optimised away as a NOP. (In my opinion it should be an error - but hey). And if you use 99.99 PERCENT you may get away with it but I would not recommend it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources