question

jdbouch3144 avatar image
jdbouch3144 asked

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

sub-querymaxcorrelated
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.

0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.