Building Complex Sql queries using group by, having, Rank and top functions

 Sql Query with Where clause

select

ProductSubcategoryID, size, StandardCost, ListPrice, class, style

from 

Production.Product

where

ProductSubcategoryID is not null and 

size is not null and

StandardCost <> 0.00 and 

ListPrice <> 0.00 and 

class is not null and 

style is not null

   -- Complex SQL query with where, group by, having clauses and top, rank, dense_rank, row functions

select

Top 20

ProductSubcategoryID, 

Max(StandardCost) as MaximumStdCost, 

Max(ListPrice) as MaxListPrice,

Max(ListPrice) - Max(StandardCost) as MaxDifferenceStandardFin,

Max(ListPrice) / Max(StandardCost) as CostPriceRatio,

Rank() over( Order by(Max(ListPrice) / Max(StandardCost)) Desc) as DistinctRankMaximumCostPriceRatio,

Row_Number() over( Order by(Max(ListPrice) / Max(StandardCost)) Desc) as RankMaximumCostPriceRatio,

DENSE_RANK() over( Order by(Max(ListPrice) / Max(StandardCost)) Desc) as DistinctRankwithNextSequence

from 

Production.Product

where

ProductSubcategoryID is not null and

StandardCost <> 0.00 and 

ListPrice <> 0.00

Group by 

ProductSubcategoryID

having 

Max(ListPrice) / Max(StandardCost) > 2.2

order by

Max(ListPrice) / Max(StandardCost) desc

Watch the below video for more details