The SQL Ideas

As per requirement in custom logic, we need to require CTE (Common Table Expression) something like for the  hierarchy , to find duplicate and remove data or for some other stuff. Recently i have used CTE within CTE for one logic and maxrecursion option as well.  So we will look at how we can use CTE inside CTE or multiple CTEs . Let us create one requirement. The requirement is we need to find the first and second objects by object types in the database and it should be in ascending order.  The sample data will be created from the script below. Let us create it first, then we will demonstrate for the logic that need to be created as per requirement. USE DEMO GO -- Creating sample table IF(OBJECT_ID('TblCTEwithCTE','U') > 0) DROP TABLE TblCTEwithCTE CREATE TABLE TblCTEwithCTE ( ObjectNumber INT , ObjectType VARCHAR(50), ObjectName VARCHAR(100), ObjectCreateDate DATETIME ) GO -- Inserting sample records created above INSERT INTO TBLCTEWITHCTE ( Obje