Find max in report

[adaniel058]

My teacher has assigned us a HW to do and I can not seem to figure out how to group the results the way he wants them.
What we have is a database of properties and owners. He wants us to generate a report that contains only the Owner names who have the most properties for rent per branch office respectivly. There are 9 branches and this is what I have so far

SELECT bNo, fName || ‘ ‘ || lName Owner, COUNT(pNo) properties
FROM Property NATURAL JOIN Staff
GROUP BY bNo, fName, lName
ORDER BY bNo;

This is small snipet of what the output looks like from what Ive done:
I know this is probably not something you usually help with but any guidance would be much appreciated.

BNO OWNER PROPERTIES
---- ------------------------- ----------
B001 ALICE MIKEL 14
B001 CARL A LOGSDON 8
B001 CARRIE WHITE 4
B001 DAVID KAYE TIERCE 24
B001 DEBBIE LYNN BRIDGES 19
B001 EDWIN TUTOR 14
B001 GAUS WILSON TIERCE 23
B001 JAMES BARKLEY 13
B001 JEFFREY CASH 15
B001 MATTHEW C LOUCKS 16
B001 RHONDA BORSTORFF 17
B001 SAMUEL BLAIR 22
B001 TONNA P FINDLEY 18
B001 WILLIAM C BAILEY 14
B001 WILLIAM S FARMER 16
B002 ALICIA T VANSANDT 22
B002 AMY SUE SMITH 20
B002 ANTOINE SANDERS 18
B002 ASHLEY G RILEY 16
B002 BROOKE NURSE 11
B002 CAMERON WELCH 21
B002 DEAN BAXLEY MCINTYRE 19
B002 GINA HARTSAW 15
B002 JENNIFER SINGLETON 22
B002 JIMMY CLARK 25
B002 JIMMY MICHAEL STEWART 16
B002 JOEL BARNES 25

[baki100]

in the order by clause you could add the COUNT(pNo) desc, this will sort the branch no in ascending order and the highest property count will be on top for that branch or you could just sort it on the count. there are actually couple of ways like using max or nested selects etc.

[jcarbillon]

you might find this link helpfull

http://www.techonthenet.com/sql/max.php

give us a feedback.