SQL Server - get the elements having max count

Asked By muthuraman alexander on 28-Sep-11 08:09 AM
hi ALL,
i want to get the elemets which having maximum count

i tried by using a temp table......
like this



SELECT RLCID,R_Current,COUNT(R_Current)as rc_cnt,Y_Current,
                  COUNT(Y_Current)as yc_cnt,B_Current,COUNT(B_Current)as bc_cnt
                  INTO #tempRLC FROM  View_Report_Consolidated_Report
                  GROUP BY RLCID,R_Current,Y_Current,B_Current


Select
VRCR.RLCID,VRCR.SIM_NO,VRCR.SCNo,VRCR.Phase,
FROM View_Report_Consolidated_Report VRCR

LEFT OUTER JOIN
(   SELECT RLCID,AVG(R_Current) AS R_C,AVG(Y_current) AS Y_C,AVG(B_Current) AS B_C FROM #tempRLC  
                      where rc_cnt = (
                                      select max(rc_cnt)
                                      from #tempRLC  as f
                                      where f.RLCID = #tempRLC .RLCID
                                      )AND
                            yc_cnt= (
                                      select max(yc_cnt)
                                      from #tempRLC  as f
                                      where f.RLCID = #tempRLC .RLCID
                                      )AND
                            bc_cnt= (
                                      select max(bc_cnt)
                                      from #tempRLC  as f
                                      where f.RLCID = #tempRLC .RLCID
                                      )
                      GROUP BY RLCID
) AS a             
ON a.RLCID=VRCR.RLCID
WHERE CONVERT(VARCHAR,VRCR.Date,101)='06/25/2011'

GROUP BY VRCR.RLCID,VRCR.SIM_NO,VRCR.SCNo,VRCR.Phase, a.R_C,a.Y_C,a.B_C

ORDER BY RLCID ASC
             
DROP TABLE #tempRLC


it showing the correct result but i need to optimize the query because it taking too much time to execute
 (about 45 - 50 secs of response time)

i need advise for this

any body please help me for this

thanks in advance
MUTHU

Web Star replied to muthuraman alexander on 28-Sep-11 08:17 AM
Actually you are using lots of sub query thats why response are very slow, for increase response time you use temp table instead of such type of sub query that will improve performance.
smr replied to muthuraman alexander on 28-Sep-11 09:40 AM
HI

The syntax for the MAX function is:
 
  SELECT MAX(expression )
  FROM tables
  WHERE predicates;
 
 
Simple Example
 
For example, you might wish to know the maximum salary of all employees.
 
  SELECT MAX(salary) as "Highest salary"
  FROM employees;

refer
http://www.techonthenet.com/sql/max.php