SQL Server Performance Forum – Threads Archive
Retrieving records randomly in SELECT StatementHi,
I am retrieving some records from a table. I am using select statement with order by clause and also distinct keyword. Every time getting records randomly not in one order when ever I execute the select statement. This is happend on Production database only. When I run the same query on development server, there is no issues. What I need to do to get records every time in one order. Please help me in this. Thanks in-advance.
Which client application are you using to issue the query – Enterprise Manager, Query Analyzer, or an external tool like MS Access?
I am using Query Analyzer.
Show us the exact statement. Perhaps the ORDER BY is in a derived table, not in the main query?
Here is the example select distinct a.email, a.id,a.lastname + ‘, ‘ + a.firstname + ‘ ‘ + isnull(a.midname,”) as NAME,MANAGERID
from Table1 a where a.id in (select id from Table1 where
Table1.STATUS != ‘INACTIVE’) and id not in (select distinct personid from Table2 where status=’ACTIVE’ and
definitionid=3) order by MANAGERID
The column on which you’re ordering is ManagerID. Perhaps you see numbers, but the actual column may be character-based instead of numeric. In that case, the ordering is strictly alphabetical: 1-10-11-2-3333-4-55-6
Yes, ManagerID column is varchar. What is the solution for this? please let me know.
If ManagerID is stricly a number without any characters then a CAST(ManagerID AS INT) should work. —
Microsoft SQL Server MVP
Yeah, I tried as Frank Kalis said. Its working fine.
Thanks for your help.
Do verify that the system is forcing digits-only for the ManagerID, or you might get into problems later on. If someone manages to enter a ManagerID with a specific character like "A", then this query … SELECT CAST(ManagerID AS INT)
FROM MyTable … will return only the purely numeric entries, plus an error message … … whereas this query … SELECT ManagerID
ORDER BY CAST(ManagerID AS INT) … will not return any rows, only the error message.
Glad to hear it worked out for you. To piggy-back on Adriaan: I would say the "best" way to address this issue is to change the underlying data type to a numerical one. —
Microsoft SQL Server MVP
quote:Originally posted by vganilkumar Yes, ManagerID column is varchar. What is the solution for this? please let me know.The BEST slotion is change it to integer datatype Other approach would be Order by len(ManagerID),ManagerID Madhivanan Failing to plan is Planning to fail