SQL - Displaying Entries That Are the Max of a Count

SQL Query to find MAX values based upon count and some filters

You don't need a subquery for your query:

select node, count(*) 
from test
where Ng LIKE 'g%'
group by node;

Getting one maximum is easy (using limit), but you want all of them. One method uses a subquery and a having clause:

select node, count(*) 
from test
where Ng LIKE 'g%'
group by node
having count(*) = (select count(*)
                   from test t2
                   where t2.Ng LIKE 'g%'
                   group by t2.node
                   order by count(*) desc
                   limit 1
                  );

This is more complicated than it needs to be because MySQL (pre-8.0) supports neither window functions nor CTEs. Happily, the syntax is simpler in MySQL 8+.

Making a MAX() query from a subquery with COUNT()

You can do:

with
x as (
  select
    b.idBoat,
    b.Name,
    count(*) as cnt
  from trip t
  join boat b on b.idBoat = t.idBoat
  group by b.idBoat, b.Name
),
m as (
  select max(cnt) as max_cnt from x
)
select 
  x.*
from x
join m on m.max_cnt = x.cnt

Get records with max value for each group of grouped SQL results

There's a super-simple way to do this in mysql:

select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

This works because in mysql you're allowed to not aggregate non-group-by columns, in which case mysql just returns the first row. The solution is to first order the data such that for each group the row you want is first, then group by the columns you want the value for.

You avoid complicated subqueries that try to find the max() etc, and also the problems of returning multiple rows when there are more than one with the same maximum value (as the other answers would do)

Note: This is a mysql-only solution. All other databases I know will throw an SQL syntax error with the message "non aggregated columns are not listed in the group by clause" or similar. Because this solution uses undocumented behavior, the more cautious may want to include a test to assert that it remains working should a future version of MySQL change this behavior.

Version 5.7 update:

Since version 5.7, the sql-mode setting includes ONLY_FULL_GROUP_BY by default, so to make this work you must not have this option (edit the option file for the server to remove this setting).

How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?

You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields:

SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

How to select the highest value after a count() | Sql Oracle

Oracle, and as far as I know - only Oracle, allows you to nest two aggregate functions.

SELECT max (f.name) keep (dense_rank last order by count (*)) as name
from author f
JOIN book b on b.tittle = f.book
Group by f.name

In order to get ALL top authors:

select   name
from    (SELECT f.name,rank () over (order by count(*) desc) as rnk
         from author f
         JOIN book b on b.tittle = f.book
         Group by f.name
         ) 
 where   rnk = 1

Since Oracle 12c:

SELECT f.name
from author f
JOIN book b on b.tittle = f.book
Group by f.name
order by count (*) desc
fetch first row /* with ties (optional, in order to get all top authors) */

How to find the top 5 values using max() in sql

solved..by using this query

SELECT TOP 5 ProductName, COUNT(ProductName) AS value_occurrence FROM OrderDetails GROUP BY ProductName ORDER BY value_occurrence DESC

How to get MAX value out of the GROUPs COUNT

Here's a query that gets the highest mark per student, put it at the top of your sql file/batch and it will make another "table" you can join to your other tables to get the student name and the subject name:

WITH studentBest as 
SELECT * FROM(
  SELECT *, ROW_NUMBER() OVER(PARTITION BY studentid ORDER BY mark DESC) rown  
  FROM marks) a 
WHERE rown = 1)

You use it like this (for example)

--the WITH bit goes above this line
SELECT *
FROM
  studentBest sb 
  INNER JOIN
  subject s
  ON sb.subjectid = s.subjectnumber

Etc

That's also how you should be doing your joins

How does it work? Well.. it establishes an incrementing counter that restarts every time studentid changes (the partition clause) and the numberin goes in des ending mark order (the order by clause). An outer query selects only those rows with 1 in the row number, ie the top mark per student

Why can't I use group by?

You can, but you have to write a query that summarises the marks table into the top mark (max) per student and then you have to join that data back to the mark table to retrieve the subject and all in it's a lot more faff, often less efficient

What if there are two subjects with the same mark?

Use RANK instead of ROW_NUMBER if you want to see both

Edit in response to your comment:

An extension of the above method:

SELECT * FROM
(
  SELECT *, ROW_NUMBER() OVER(PARTITION BY su, st ORDER BY c DESC) rn FROM
  (
    SELECT studentid st, subjectid su, count(*) c
    FROM marks
    GROUP BY st, su
  ) a
) b
INNER JOIN student stu  on b.st = stu.studentnumber
INNER JOIN subject sub on b.su = sub.subjectnumber
WHERE
  b.rn = 1

We count the marks by student/subject, then rownumber them in descending order of count per student-subject pair, then choose only the first row and join in the other wanted data

---

Related Topics