Correlated Sub-query with Max - Sql Server 2014
I have a query like this: Select something FROM tablea JOIN tableb ON tablea.employee_id = tableb.employee_id AND entity_id = 1 AND person_dtl_id = (SELECT MAX(person_dtl_id) from detail det where det.person_id = person_id and det.employee_id = employee_id and det.entity_id = Ientity_id). Can you have multiple Where Predicates like this in the Correlated Subquery with MAX or is it more efficient to use TOP 1. Thanks
Please could you format your SQL Coded using the CODE button in the text editor. It helps us to read the SQL.
Can you have multiple Where Predicates like this in the Correlated Subquery with MAX
Yes, WHERE clauses can take used multiple arguments at a time. MAX doesn't impact how many arguments can be be used in a WHERE clause.
is it more efficient to use TOP 1
It depends! When I'm developing I like to try multiple different ways to see what the query and results look like on our server. However, I might then revisit it in the future as something that works well with 50k results now might not work well with 3m rows in the future.
But Yes, I have used TOP 1 like this in the past with CROSS APPLY.
