Answer / karthick veerappan

select top 1 region,count(*) from table2 where empid in
(select empid from table1 where tsize=3) group by region
order by count(*) desc

Answer / sitaram karancheti

In Oracle:

SELECT COUNT(table1.emp_id), region
FROM table1, table2
WHERE tshirt_size = 3 AND table1.emp_id = Table2.emp_id
AND rownum < 2
GROUP BY region
ORDER BY COUNT(table1.emp_id) DESC

In MySql:

SELECT COUNT(table1.emp_id), region
FROM table1, table2
WHERE tshirt_size = 3 AND table1.emp_id = Table2.emp_id
GROUP BY region
ORDER BY COUNT(table1.emp_id) DESC LIMIT 1

Answer / pradip jain

select top 1 region,count(t2.eid) a
from t2
join t1 on t1.eid=t2.eid
group by region,[T-Shirt_Size]
having [T-Shirt_Size]=3
order by a desc

Answer / pradip jain

Himesh is also correct with little change..

select top 1 region,count(*) e
from t2 JOIN t1 ON (t1.eid = t2.eid and
t1.[T-Shirt_Size] = 3)
group by region
order by e desc

Answer / susanna

select top 1 Count(r.Empid) as Ct,r.regionId from Emp_Region
r inner join Emp_Tshirt t on r.Empid= t.EmpId
where t.T_size=3 group by r.regionId order by Ct desc

Answer / avaneesh bajoria

select top 1 t2.region , count(t1.tsize) as co from tsize
t1,region t2
where t1.empid = t2.empid
group by t2.region,t1.tsize
having t1.tsize = 3 order by co desc

Answer / ashok

select b.Region, a.TSize, Count(a.TSize)
as TSizecount from Table1 a,Table2 b where a.Empid =
b.Empid and a.TSize=3 group by region, TSize order by
Region, count(*), TSize desc

Answer / lakram5455

SELECT a.Region FROM
(SELECT TOP(1) a.TSize, COUNT(a.EId) AS TotalCount, b.Region
FROM emp a INNER JOIN Region b ON b.EId = a.EId WHERE TSize
= 3 GROUP BY a.TSize, b.Region ORDER BY TotalCount DESC) a

Answer / anil gupta

select region, count(t1.size) from tab1 t1 inner join tab2
t2 on t1.eid = t2.eid where t1.size = 3 group by t2.region
order by count(t1.size) DESC and rownum = 1;

Answer / ramesh babu

Create table #temptshirt(empid int,tshirt int)
Create table #tempregion(empid int,region varchar(200))

Insert into #temptshirt(empid,tshirt)values(100,2),(102,4),
(103,5),(104,3),(105,3),(106,3),(107,5),(108,6),(109,3),
(110,4),(111,3)
Insert into #tempregion(empid,region)values(100,'SA'),
(102,'SA'),(103,'UK'),(104,'UK'),(105,'PHL'),(106,'US'),
(107,'US'),(108,'US'),(109,'RSA'),(110,'RSA'),(111,'UK')

Select Top 1 T.region,T.Total
from
(
Select b.region,COUNT(*)[Total] from #temptshirt a inner
join #tempregion b on a.empid=b.empid
Where a.tshirt=3
group by b.region
)T
order by 2 desc

What do you mean by an execution plan? How would you view it?

0 Answers  

What is coalesce and check constraint in sql server?

0 Answers  

Is it possible we can connect our SQL database to GUI application? If it is yes than how tell me the explanation

2 Answers  

what is cluster and nin-cluster index?

4 Answers  

What are the steps you will take to improve the performance of a poor performing query?

0 Answers  

How will u find the query which is running in some other machine

4 Answers   CarrizalSoft Technologies, IBM,

What is the difference between varchar and varchar types?

0 Answers  

Explain comment on transactions?

0 Answers  

which database is best to use in oracle and sql server? explain reasons?

5 Answers   TCS,

Which stored procedure will you be running to add a linked server?

1 Answers  

explain what is a schema in sql server 2005? Explain how to create a new schema in a database? : Sql server database administration

0 Answers  

as a general practice, it is recommended to have dbo be the owner of all database objects however, in your database you find number of tables owned by a user other than dbo, how could you fix this? : Sql server administration

0 Answers