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

How do you generate rDoc for a particular plugin using rake

ruby-on-rails ruby rake rdoc

OK, I am trying to generate the rDoc for paperclip, but the rake task is choking on another plugin before it gets to it, so this is out of the question:

How to Fix Error UnsatisfiedLinkError: C:\Program Files\dtSearch Developer\bin\dtsjava.dll: The operating system cannot run %1

java windows-xp java-native-interface dtsearch

I am working with J2EE.I have installed the dtSearch Engine Tool on my windows xp,I have set all the environment varibles for this still I got the above Error on my console.