EXISTS vs INNER JOIN

Hi All -

I know the data is unseen and my pose a problem visualizing whats going on here, and I will post sample data if needed, but Ill try first w/o data. I mainly need to know if/how I am incorrectly using EXISTS.

my key on vwAssays is LotID,AssayRank I am trying grab just the records out of vwAssays for each Lot with the greatest AssayRank

LotID AssayRank 1 0 1 1 1 2 2 0 2 1 2 2 3 0 3 1 3 2

#1

SELECT *

FROM vwAssays a

WHERE exists (select lotid, max(assayrank) assayrank from vwAssays l where a.lotid = l.lotid and a.assayrank = l.assayrank group by lotid ) and runid = 122

order by lotid

#2

SELECT *

FROM vwAssays a inner join (select lotid, max(assayrank) assayrank from vwAssays group by lotid) l on a.lotid = l.lotid and a.assayrank = l.assayrank

where runid = 122

order by a.lotid

#3

select lotid, max(assayrank) assayrank from vwAssays l where runid = 122 group by lotid

#1 does not work, it is showing all assayranks for each lotid

#2 works ok but Id like to use Exists instead

my actuall question is what needs to happen to #1 so that it does what #2 does.

Hi there,

I would just use solution #2 and move on. The main thing you need to know about EXISTS is that it only returns either True or False, therefore the only way to get to True or False is to basically use the logic you have in #2.

|||

using CTE:

Code Snippet

WITH mycte AS (SELECT LotID, AssayRank, runid, Row_Number() OVER (PARTITION BY LotID ORDER BY AssayRank DESC) AS numRank
FROM vwAssays

WHERE runid = 122)
SELECT LotID, AssayRank FROM mycte WHERE numRank = 1