Results 1 to 7 of 7

Thread: Join & Sort?

  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Question Unanswered: Join & Sort?

    I have this 2 SQL Query that gives the same result:

    Code:
    select 
       t.workorderid,
       t.employeeid 
    from temp2 as t 
    
    OR
    
    select 
       t.workorderid,
       t.employeeid 
    from temp2 as t 
    left join employee as e on t.employeeid=e.employeeid
    ** RESULTS **
    WorkOrderID - EmployeeID
    1215 10197
    1216 10209
    1217 10217
    1218 10217
    1219 10231
    1220 10234
    1221 10264
    1222 10284
    1223 10322
    1224 10323
    1225 10324
    1226 10329
    1227 10334

    Now, when I add ANY FIELD from the employee table to include on the view, it sorts everything out on the <employeeid> field which I don't want to happen. Is it possible not to get this auto sort?

    Code:
    select 
       t.workorderid,
       t.employeeid,                
       e.firstname                   <--added
    from temp2 as t 
    left join employee as e on t.employeeid=e.employeeid
    WorkOrderID - EmployeeID - FirstName
    3209 10000 OPEN
    6058 10000 OPEN
    4594 10197 VIRGINIA
    4925 10197 VIRGINIA
    6411 10197 VIRGINIA
    1215 10197 VIRGINIA
    3330 10197 VIRGINIA
    3661 10197 VIRGINIA
    6734 10199 STEPHANIE CHARLYNN
    7157 10199 STEPHANIE CHARLYNN
    5964 10199 STEPHANIE CHARLYNN
    5965 10199 STEPHANIE CHARLYNN
    6733 10199 STEPHANIE CHARLYNN

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Join & Sort?

    For an explicite sort order, there is this ORDER BY clause in SQL, all other orders are not guarrantied, but the result of the query process. When SQL Server decides to reorganize it's internal page allocations, the result may be in another order.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    So I guess it means that I would have no choice in the manner SQL Server would present the data to me. And based on the last query, it would always have it sorted via EMPLOYEEID. Hmmm.....

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    No, my point was to add an ORDER BY clause, to control your sort order as you require. If you need assistence of using the ORDER BY, consult your T-SQL syntax and look up SELECT.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Thanks. I got your point on specifying an explicit order. However, I want to view my results UN-SORTED just like on the the first 2 queries.

    However as I pointed out, when I add a field from the employee table, even without me specifying a sort order, the records are presented to me...well... sorted by <employeeid>.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    arprince, when you run a query without an ORDER BY, you are getting the results unsorted

    just because you get a different apparent order depending on the presence of the employee column does not mean that it is sorted by employeeid

    if it seems that way, it's just a coincidence

    in fact, it is most likely due to the way the optimizer executes the query

    with the employee field included, the left outer join must now actually look at employee rows, and it is probably using a clustered index, which of course could easily change the sequence of result rows

    i'm not a DBA, so performance and optimizer nuances like that don't really matter to me

    what is very clear is this: any result set order you get without an ORDER BY is not guaranteed to be in any particular order at all, despite the fact that it seems to be

    if you want them in a particular order, such as work order number, then you have to ask for it that way


    rudy
    http://r937.com/

  7. #7
    Join Date
    Sep 2003
    Posts
    522
    it is either PK or clustered index that is present on employeeid. you can try to include (index=0) after employee as e, but that will not guarantee that the index/PK will not be selected by the optimizer when returning recordset to the client.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •