How to find highest count of result set using multiple tables in SQL (Oracle)

sql max count per group
sql count max occurrences
sql max count subquery
sql max group by
sql select max value from multiple columns
sql where max value
finding the highest count in a group by query
sql max date

I have four tables. Here are the skeletons...

ACADEMIC_TBL
    academic_id
    academic_name


AFFILIATION_TBL
    academic_id*
    institution_id*
    joined_date
    leave_date

INSTITUTION_TBL
    institution_id
    institution_name

REVIEW_TBL
    academic_id*
    institution_id*
    date_posted
    review_score

Using these tables I need to find the academic (displaying their name, not ID) with the highest number of reviews and the institution name (not ID) they are currently affiliated with. I imagine this will need to be done using multiple sub-select scripts but I'm having trouble figuring out how to structure it.

this will work:

SELECT   at.academic_name, 
         it.institution_name, 
         Max(rt.review_score), 
from     academic_tbl at, 
         affiliation_tbl afft, 
         institution_tbl it, 
         review_tbl rt 
WHERE    AT.academic_id=afft.academic_id 
AND      afft.institution_id=it.institution_id 
AND      afft.academic_id=rt.academic_id 
GROUP BY at.academia_name,it.instituton_id

SQL: MAX Function, This SQL tutorial explains how to use the SQL MAX function with syntax and examples. As a result, "Highest salary" will display as the field name when the result set Each time a report is run in Oracle, a record is written to this table noting the above info I would like to find the customer_id, who has Highest order count. Using these tables I need to find the academic (displaying their name, not ID) with the highest number of reviews and the institution name (not ID) they are currently affiliated with. I imagine this will need to be done using multiple sub-select scripts but I'm having trouble figuring out how to structure it.

Can I do a max(count(*)) in SQL?, SELECT MAX(y.num) FROM (SELECT COUNT(*) AS num FROM TABLE x) y Just order by count(*) desc and you'll get the highest (if you combine it with limit 1 ) Best way to get result count before LIMIT was applied Using max with a limit will only give you the first row, but if there are two or more rows with the same  Limitations of Execute WITH RESULT SETS in SQL Server . 1. We cannot remove any column from an existing result set. If the result set is giving three columns as output, we need to define all three columns using the WITH RESULT SETS option. 2. We cannot change the order of the columns in the result set. 3. WITH RESULT SETS cannot be nested with

Try this one:

select
       inst.institution_name
     , aca.academic_name
from
       academic_tbl    aca
     , institution_tbl inst
     , affiliation_tbl aff
     , review_tbl      rev
     , (
              select
                     max(rt.review_score) max_score
              from
                     review_tbl      rt
                   , affiliation_tbl aff_inn
              where
                     rt.date_posted     >= aff_inn.join_date
                     and rt.date_posted <= aff_inn.leave_date
                     and rt.academic_id = aff_inn.academic_id
                     and rt.institution_id  = aff_inn.institution_id
       )
       agg
where
       aca.academic_id         = inst.academic_id
       and inst.institution_id = aff.institution_id
       and aff.institution_id  = rev.institution_id
       and aff.academic_id     = rev.academic_id
       and rev.date_posted    >= aff.join_date
       and rev.date_posted    <= aff.leave_date
       and rev.review_score    = agg.max_score
;

It might return more than one academic, if there are more with the same score (maximum one).

Finding the highest COUNT in a GROUP BY query, I want to get the maximum of totalcontracts from the retrieved data using I retrieve two columns named SuperVisor and totalcontracts using Just use your first COUNT query as a derived table in a new MAX Dig Deeper on Oracle and SQL SAP's Climate 21 program sets sights on global sustainability. SQL: Using IN operator with a Multiple Row Subquery. IN operator is used to checking a value within a set of values. The list of values may come from the results returned by a subquery.

SQL MAX count() and having, The sql having also be used with sql max function. In this part, you will see the usage of SQL COUNT() along with the SQL MAX(). alias 'mycount' from the '‚Äčorders' table with the following condition -. 1. 'agent_code' for a group will be equal to the result of an outer query [SELECT MAX(agent_code). The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows.

Pramatic Uses of The Oracle MAX() Function, The Oracle MAX() function is an aggregate function that returns the maximum value of a set. We will use the products and product_categories tables in the sample To get the most expensive product information, you use the following query: you can use the product category name to make the result set more readable. or use a subquery: select count (*) from (select distinct col1, col2 from mytable); So I am looking for something along the lines of: select count (distinct col1, col2) from mytable; I don't know whether the SQL standard allows multiple values for COUNT. If not, we might use tuples: select count (distinct (col1, col2) ) from mytable; or

The Ultimate Guide to Oracle GROUP BY with Examples, This tutorial shows you how to use the Oracle GROUP BY clause to group rows into to group rows into a set of summary rows by values of columns or expressions. with aggregate functions such as AVG() , COUNT() , MAX() , MIN() and SUM() . the GROUP BY clause to find unique order statuses from the orders table:. The SQL COUNT function is one of the most common functions used by SQL developers. I use it on a daily basis. Learn more about the COUNT function in this article. This article applies to Oracle, SQL Server, MySQL, and PostgreSQL.

Comments
  • Please provide sample data and output.
  • It is likely that this query will generate an error because some of the SELECTed columns do not belong to the GROUP BY clause
  • You should really stop using those ancient and fragile implicit joins in the WHERE clause and start using "modern" (more than 25 years old) explicit JOIN operators
  • @a_horse_with_no_name i dont use modern i am used to ancient joins syntax that works in my office i cant help it . My manager has asked me to use old syntax only.