T-sql, Sql Server Compact Edition, Alias For Select

How can I optimize my SQL code ? I want to add alias. I am using SQL Server Compact Edition. ( ... ) is a SELECT query SELECT * FROM ( ... ) WHERE id IN ( SELEC

Solution 1:

I would suggest to use that query only once. You can create a CTE of that query and then write the query as follows:

with cte
As
(....)
Select*from cte 
    where id in 
        (select id from cte 
            groupby id 
            havingcount(*) >1)

Hope it helps

Solution 2:

this is another option:

SELECT*FROM
(SELECT*, COUNT(*) OVER(PARTITIONBY id) ids FROM (...)) x
WHERE ids>1