CODEWITHSUNDEEP
sql-servert-sqljoin
user3357885
user3357885

Reputation: 23

Optimal way to find last call attempt - CTI database

I'm using a MSSQL database as backend for small CTI application and I'm wondering if there is a better way to query last call attemp for each client called in CTI database.

I got two tables CUSTOMERS and CALLS. CALLS store a history of all calls with foreign key - CUSTOMER_ID as link to CUSTOMER table).

My query looks like:

SELECT * FROM CALLS x 
JOIN 
(SELECT MAX(CALL_ID), CUSTOMER_ID FROM CALLS GROUP BY CUSTOMER_ID) y 
ON 
x.CALL_ID = y.CALL_ID

With couple tousands of records in calls tables and couple other joins it's getting to take a bit too much time. I would be grateful for some ideas how to speed it up.

Shag

Upvotes: 2

Views: 365

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Try this. Use window function which will avoid hitting the same tables twice

SELECT *
FROM  (SELECT Row_number() OVER(partition BY CUSTOMER_ID ORDER BY CALL_ID DESC) rn,
              *
       FROM   CALLS) a
WHERE  rn = 1

Upvotes: 1

Related Questions