|
View previous topic :: View next topic
|
| Author |
Message |
sunnybunny
New User
Joined: 29 Nov 2005
Posts: 49
|
|
|
|
Hello Friends,
I want to execute a query with following requirement.
I have a table with two fields A and B. The value of A field can occur morethan once in B field.
For ex if the values A and B are as follows.
| Code: |
A B
100 100
100
100
200 200
200
300 300
300
300
300 |
Now I want to find out the maximum no.of Bs. That means in above example 300 comes as 4 times. So I want that number.
Could you please provide me the SQL query for that.
Regards,
Suneel. |
|
| Back to top |
|
 |
kussu
Warnings : 1
New User
Joined: 16 May 2007
Posts: 33
Location: India
|
|
|
|
Let the table name be Alpha
Select count(b)
From alpha
Where b= 100 |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello Pyari,
Please re-read the requirement.
Your suggestion will count the number of colB rows with a value of 100. That is not the requirement.
The requirement is to identify which value in colB is present in the most rows.
Suneel - please post sample data where colA and colB do not always have the same value or explain why there would be real data like the rows you defined. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
kussu's solution will only count the number or 100's that exist and would require you to execute for each possible value in Column B then pick the highest count.
| Code: |
SELECT MAX(TT.CountVal)
FROM (SELECT B, SUM(1) as CountVal
FROM table
GROUP BY B) TT
|
Will get you the highest count, but it won't tell you which one has that count. Do you need to know which one has that highest count also?
| Code: |
SELECT TT.A, TT.B FROM
(select B, count(B)
from table
group by A) TT
WHERE TT.B = (SELECT MAX(TT2.B) FROM (select A, count(B)
from table
group by A)TT2)
|
This also works
| Code: |
SELECT B, Count(B)
FROM table
GROUP BY B
HAVING COUNT(B) = (SELECT MAX(TT.C) FROM (select count(B) as C
FROM table
GROUP BY B) TT)
|
|
|
| Back to top |
|
|
|
|
