SQL Server Display details starting ID having maxcount and on

Question

I have SQL Query to display all product details. My product table has DefectID(which says defect on the product), and the same defect ID is repeatable for different products.I need to display product details starting with defectID that has occurred maximum count and then next maximum and so on.

Answer 1

try using Count() and Over()

;with cte as
(
    select Prod_Id,Prod_Desc,..., Count(*) Over(Partition by DefectID) as ct1 from My_Product
}
select * from cte order by ct1 desc

Answer 2

• First calculate defects counts
• Then assign an id to each defect to show rank
• Then join back to your product table and order for defect rank

.

 WITH countDefect as (
      SELECT DefectID, COUNT(defectID) cTotal
      FROM Products 
      GROUP BY DefectID
 ),
 rankDefect as ( 
     SELECT DefectID, row_number() over (order by cTotal DESC) as dRank
     FROM countDefect 
 )
 SELECT Product.*, R.dRank
 FROM Products P 
 INNER JOIN rankDefect  R
    ON P.DefectID = R.DefectID
 ORDER BY R.dRank