SQL Server Performance

Retrieving records randomly in SELECT Statement

Discussion in 'SQL Server 2005 General Developer Questions' started by vganilkumar, Apr 12, 2007.

  1. vganilkumar New Member

    Hi,
    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.
  2. Adriaan New Member

    Which client application are you using to issue the query - Enterprise Manager, Query Analyzer, or an external tool like MS Access?
  3. vganilkumar New Member

    I am using Query Analyzer.
  4. Adriaan New Member

    Show us the exact statement. Perhaps the ORDER BY is in a derived table, not in the main query?
  5. vganilkumar New Member

    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

  6. Adriaan New Member

    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
  7. vganilkumar New Member

    Yes, ManagerID column is varchar. What is the solution for this? please let me know.
  8. FrankKalis Moderator

    If ManagerID is stricly a number without any characters then a CAST(ManagerID AS INT) should work.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  9. vganilkumar New Member

    Yeah, I tried as Frank Kalis said. Its working fine.
    Thanks for your help.
  10. Adriaan New Member

    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
    FROM MyTable
    ORDER BY CAST(ManagerID AS INT)

    ... will not return any rows, only the error message.
  11. FrankKalis Moderator

    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.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  12. Madhivanan Moderator

    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

Share This Page