Data Engineering Technical Blog

What is a correlated subquery ? Correlated subquery is a sql query where the subquery portion depends on the main/outer query to interpret its result.  For example if you want to get 2nd max SalesAmt you can use the correlated subquery as below. Create table #temp ( Country VARCHAR(10), [State] VARCHAR(10), SalesAmt Money ) INSERT INTO #temp VALUES('India','Karnataka',50000),('India','Andhra',30000),('India','Telangana',15000), ('USA','Texas',50000),('USA','NY',32000),('USA','Redmond',15000), ('UK','London',50000),('UK','Reading',30100),('UK','Edinburg',15000) SELECT * FROM #temp T WHERE T.SalesAmt = ( SELECT MAX(SalesAmt) Amt FROM #temp A WHERE SalesAmt < (SELECT MAX(SalesAmt) FROM #temp WHERE A.Country = Country) AND  A.Country = T.Country) There are easier methods to get 2nd max like using Ranking functions. You can also combine Gro...