I have been asked to return the most popular record from a group of results.

can you explain why you use self join?

I am not doing anything yet.

I have three columns, with data. I need to return the highest SearchCount for each city?

City      Checked      SearchCount
Brighton      r      10
Brighton      r      20
Brighton      a      10
London      a      50
London      a      60
London      a      70
London      b      80
London      g      90

I suppose i do

        Select City     ,Checked,      Max(SearchCount)
        from tb1
        group by City,      Checked  

What about my approach... will that work

 Select City     ,Checked,      Max(SearchCount)
        from tb1
        group by City,      Checked  

Since you have data and underlying table. So give it a try and if it works then its great.

Thanks... I was just checking that my approach was not totaly wrong!

thanks

Hi Author,

Its depend, what do you wanted.
Your approach work but Searchcount will take maximum per city & checked.
And will return ONE ROW Per City & Checked

Below query will be fetch data with maximum searchcount irrespective of city & checked.
Select * from A where SearchCount = (Select Max(SearchCount) from A)
And will return only ONE ROW

I used the following SQL.

Select
ID,
City,
Checked,
SearchCount
from A where SearchCount = (Select Max(SearchCount) from A where city = 'London')
and city = 'London'

HI,

You can use this too(you must be knowing still)

Select
ID,
City,
Checked,
SearchCount
from A, (select city,Max(SearchCount)SearchCount from A) B
where A.City = B.City
And A.SearchCount = B.SearchCount