Query Runs Slowly

[TastyWheat]

I have a table with a rotating work order number (from 1 to 9999 then starts over) and because there may be more than one record with the same work order number I need to find the most recent one. Here's the query I'm using the find the most recent record for each work order number:

SELECT * FROM [Work Orders] AS wk1
WHERE NOT EXISTS(
   SELECT * FROM [Work Orders] AS wk2
   WHERE wk1.[Work Order #] = wk2.[Work Order #] AND
   wk1.[Call In Date] < wk2.[Call In Date]
);

Right now the table has a litttle over 10000 records and it takes anywhere from 10 seconds to 60 seconds to return the results. Is there a way I could possibly speed up this query?

 

[ByteMyzer]

Make sure that the fields [Work Order #] and [Call In Date] are indexed, then try this query:

SELECT wk1.*
FROM [Work Orders] AS wk1
WHERE wk1.[Call In Date]=
   (SELECT MAX(wk2.[Call In Date])
    FROM [Work Orders] AS wk2
    WHERE wk2.[Work Order #]=wk1.[Work Order #])
;

 

[TastyWheat]

Wow! That made a huge difference. Indexing alone wasn't enough. I had to change the logic of the SQL to get the results I needed. Thanks a lot!