 # Optimise Query Efficency

Hi

Does any one have any advise of how I could optimise the below query to run more efficiently?

SELECT *
FROM AATABLE AA

LEFT JOIN (
SELECT *
,ROW_NUMBER() OVER (PARTITION BY OBJECTNUMBER ORDER BY TRUE_DATE DESC) AS ROW_ID
FROM
(SELECT
A.OBJECTNUMBER,
B.TXT04,
(CAST(CONCAT(CONCAT(CAST(A.CREATEDATE AS DATE),' '),CAST(A.CREATEDTIME AS TIME)) AS TIMESTAMP)) as TRUE_DATE
FROM ATABLE A
LEFT JOIN BTBLE B ON A.STATUS = B.STATUS
WHERE A.FILTERA IN ('SOMETHING','SOMETHING2')
AND B.FILTERB <> 'SOMETHING'
)x ) C
ON AA.OBJECTNUMBER = C.OBJECTNUMBER
AND C.ROW_ID = 1

WHERE AA.QMNUM = '111111'

Reducing to one subquery

``````SELECT *
FROM AATABLE AA
LEFT OUTER JOIN (SELECT TOP(1) WITH TIES
A.OBJECTNUMBER
,B.TXT04
,(CAST(CONCAT(CONCAT(CAST(A.CREATEDATE AS DATE),' '),CAST(A.CREATEDTIME AS TIME)) AS TIMESTAMP)) AS TRUE_DATE
FROM ATABLE A
INNER JOIN BTBLE AS B
ON B.STATUS=A.STATUS
AND B.FILTERB<>'SOMETHING'
WHERE A.FILTERA IN ('SOMETHING','SOMETHING2')
ORDER BY ROW_NUMBER() OVER(PARTITION BY OBJECTNUMBER ORDER BY TRUE_DATE DESC)
) AS C
ON AA.OBJECTNUMBER = C.OBJECTNUMBER
WHERE AA.QMNUM = '111111'
;
``````

If you don't need all the columns replace * with the columns your using. Using CTEs instead of subqueries may make it faster depending on resources.