can any one answer this query,thank you in advance
Table 1 has 2 columns: EmployeeId, T shirtsize(values can
be 1,2,3)
Table 2 has 2 columns: EmployeeId, Region
Write SQL to Find the region which has the largest number
of people with Tshirt size=3
Answers were Sorted based on User's Feedback
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
Is This Answer Correct ? | 8 Yes | 5 No |
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
Is This Answer Correct ? | 4 Yes | 2 No |
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
Is This Answer Correct ? | 1 Yes | 0 No |
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
Is This Answer Correct ? | 1 Yes | 0 No |
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
Is This Answer Correct ? | 1 Yes | 0 No |
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
Is This Answer Correct ? | 4 Yes | 4 No |
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
Is This Answer Correct ? | 0 Yes | 0 No |
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
Is This Answer Correct ? | 0 Yes | 0 No |
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;
Is This Answer Correct ? | 0 Yes | 0 No |
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
Is This Answer Correct ? | 0 Yes | 0 No |
What are the 2 types of classifications of constraints in the sql server?
List the different types of joins?
Why we use functions in sql server?
What is impersonation? What are the different impersonation options available in ssas? : sql server analysis services, ssas
What does the INSTEAD OF trigger do?
Following are some of the question related to below mentioned query? select e1.salary from employee3 e1 where 2= ( select count(distinct(e2.salary)) from employee3 e2 where e2.salary>=e1.salary ) 1) What the query returns? 2) How it works? - Detail explanation (what the sub query does, why it is (where 2=)....etc...Please?
How to transfer Logins from SQL Server 2000 to 2005
What is a covered index?
How to insert data with null values?
What is the difference between a local and a global temporary table?
What is an index?
Why I can not enter 0.001 second in date and time literals in ms sql server?