SQL Query reult output sequence changes using RANK (Top 2*)
Hello Experts,
Any ideas why everytime i run a query in my database the sort order of the output changes from time to time.
SELECT * FROM TableA
OUTPUT 1st run:
Col1 , Col2, Col3, Col4, Col5
1 , 1 , 1 , 1 , 1
2 , 2 , 2 , 2 , 2
3 , 3 , 3 , 3 , 3
4 , 4 , 4 , 4 , 4
OUTPU 2nd run:
Col1 , Col2, Col3, Col4, Col5
2 , 2 , 2 , 2 , 2
4 , 4 , 4 , 4 , 4
1 , 1 , 1 , 1 , 1
3 , 3 , 3 , 3 , 3
The problem this poses is that i've got a stored procedure which uses ranking (or Top 2*) based on the output of the table and thus giving me inconsistent data.
Any ideas why everytime i run a query in my database the sort order of the output changes from time to time.
SELECT * FROM TableA
OUTPUT 1st run:
Col1 , Col2, Col3, Col4, Col5
1 , 1 , 1 , 1 , 1
2 , 2 , 2 , 2 , 2
3 , 3 , 3 , 3 , 3
4 , 4 , 4 , 4 , 4
OUTPU 2nd run:
Col1 , Col2, Col3, Col4, Col5
2 , 2 , 2 , 2 , 2
4 , 4 , 4 , 4 , 4
1 , 1 , 1 , 1 , 1
3 , 3 , 3 , 3 , 3
The problem this poses is that i've got a stored procedure which uses ranking (or Top 2*) based on the output of the table and thus giving me inconsistent data.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the response. But i forgot to mention that the query is neseted and is aggregated (Grouped by) to achieve ranking as required. Thus simply ordering it by won't suffice.
Hello sjwales,
Thnaks for the response. Yes your suggestion fixed my issue. In short, i added a Non-clustered index on the table on multiple columns i'm interested with.