sql max of count 2 tables

i've been searching for literally hours (1pm-11pm) for a solution to this SQL query I have to write. Basically, I have 2 tables and I have to select the ID from one table which has the maximum results in another. The second issue is there are 2 IDs. I can't quite explain what I mean because I'm that unsure but I can post my instructions and a link to the tables.

Any help will be greatly appreciated. I've also looked at a million other posts on SO and other places but even if it seems remotely relevant, I've no idea what changes to make to suit my needs.

i.e

SQL SELECT MAX COUNT

So my task is as follows:-

Display the name and the telephone number of private owners which have more properties than anybody else.

The top table in the image shows the "properties for rent" table and the lower shows the "private owners".

In reference to the question, I need to use the primary key of the private owners table to count the number of properties that each private owner has available to rent and then display the details of the private owner(s) who has the most properties available - which by studying the data, is 2 private owners (CO87 and CO93).

Again, I'd appreciate any help at all with this, I've been pulling my hair out for the best part of 12 hours :/

Thanks in advance guys,

Tim.

P.s - Just for the curious, this is one of an insane amount of SQL tasks for a university assignment =)

Edit:- The owner IDs are strings, not integers.

Answers


Your process should be:

  1. Get a count of properties for each owner
  2. Find the owner IDs with the max # of properties
  3. Find the owners with those owner ID.

Seems like this should work:

SELECT * FROM Owners
WHERE OwnerID IN 
  (
   SELECT OwnerID 
   FROM Properties
   GROUP BY OwnerID
   HAVING COUNT(*) = 
      (SELECT COUNT(*)
       FROM Properties
       GROUP BY OwnerID
       ORDER BY COUNT(*) DESC
       LIMIT 1)
  )

Need Your Help

RestSharp custom IDeserialize return generic type

c# json generics windows-phone-8.1 restsharp

I would like to write custom deserializer class by implementing RestSharp.Portable.Deserializers.IDeserialize interface:

BMI calculation C++

c++ arrays

I need some help with calculating the BMI. I have read in data from a file and populated 2 arrays that contain the weight and height of people. I am creating a 3rd array that contains the BMI of th...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.