Как найти максимальное количество sql - Исправление недочетов и поиск решений вместе с Examum.ru

Please let me know what is wrong with the below command

mysql> select max(count(*)) from emp1 group by name;
ERROR 1111 (HY000): Invalid use of group function

asked May 8, 2013 at 12:43

Try:

SELECT NAME, 
       COUNT(*) as c 
FROM table 
GROUP BY name 
ORDER BY c DESC LIMIT 1

Ergest Basha

7,5954 gold badges7 silver badges28 bronze badges

answered May 8, 2013 at 12:46

matskomatsko

21.9k21 gold badges102 silver badges144 bronze badges

From the supplied code I understand that you wish to select the highest number of employees that share the same name.

The problem with your query is that you are trying to apply more than one level of aggregation in a single scope.

Try this:

SELECT MAX(Total) FROM (SELECT COUNT(*) AS Total FROM emp1 GROUP BY name) AS Results

…or this:

SELECT COUNT(name) FROM emp1 GROUP BY name ORDER BY COUNT(name) DESC LIMIT 1

Both queries return the same result, but their implementations are different.

Use whichever is the fastest for you or whichever you prefer.

answered Feb 17, 2016 at 13:44

WonderWorkerWonderWorker

8,4594 gold badges61 silver badges73 bronze badges

I’d to the following (assuming I understand correctly what you want):

select c from
(
    select count(*) as c, name from emp1 group by name
) tmp
order by c desc limit 1

This selects the largest count from all counts by name. For example, if your table contains

Name
-----------------------
Test
Test
Hello
World
World
World

The inner select would create a «table» with this data

c         Name
----------------------
2         Test
1         Hello
3         World

The outer select would order this by c descending and select the first entry, which is 3.

This can be shortened to

select count(*) c from emp1 group by name order by c desc limit 1

answered May 8, 2013 at 12:50

Thorsten DittmarThorsten Dittmar

55.7k8 gold badges89 silver badges138 bronze badges

You are asking «what is wrong with your statement». This is your statement:

select max(count(*))
from emp1
group by name;

I understand what you mean. But a SQL Compiler does not. The reason is simple. A given select can have only one group by clause. And your query is asking for two of them. The first is the group by name. The second is an aggregation on all those results.

The proper way to write your query (as you seem to intend) is using a subquery:

select max(cnt)
from (select count(*) as cnt
      from emp1
      group by name
     ) t

This is a perfectly reasonable solution that only uses standard SQL. Other answers have proposed the solution using the limit clause, which may be a bit more efficient.

answered May 8, 2013 at 13:18

Gordon LinoffGordon Linoff

1.2m57 gold badges639 silver badges780 bronze badges

You must select name to group by it, then use max() on the result of that as a subquery:

select max(count)
from (
  select
    name,
    count(*) as count
  from emp1
  group by name) x

I have formatted the query so you can see what’s happening, rather than put it all on one line as you showed it. Btw the «x» at he fnf is a required alias for the subquery.

answered May 8, 2013 at 12:51

Bohemian♦Bohemian

409k90 gold badges570 silver badges715 bronze badges

SELECT  MAX(name_count)
FROM
        (
        SELECT  name
                ,count(*) as name_count
        FROM    emp1 
        GROUP BY
                name
        )

answered May 8, 2013 at 13:16

Declan_KDeclan_K

6,6762 gold badges19 silver badges30 bronze badges

In case the data happened to have multi occurrences for the max value the LIMIT 1 will not answer the question.
In order to illustrate this, I used the WOLRD database sample from MySQL to answer this question.

Q) Return the list of the country(ies) that has the highest number of languages.

FIVE countries fit to have the same number of languages which is 12 namely

Canada
China
India
Russia
USA

Firstly, we need to create a VIEW to hold the max value (in this case is 12)

CREATE VIEW abc AS SELECT count(countrycode) AS total FROM 
countrylanguage GROUP BY countrycode 
ORDER BY COUNT(countrycode) DESC limit 1;

then use the view in the SELECT statement below:

SELECT `name`, COUNT(country.`name`) FROM country JOIN    
countrylanguage ON country.`code` = countrylanguage.countrycode 
GROUP BY country.`name` 
HAVING COUNT(country.`name`) = (SELECT total FROM abc) ;

answered Dec 26, 2021 at 6:40

Aree AliAree Ali

551 gold badge1 silver badge6 bronze badges

you didn’t need max here i think you want know the employee count

then use just count()
like this

select count(*) from emp1

but here another example for use max with count for example we need to know most author have many books

SELECT  book_author.`author_id`
,  COUNT(book_author.`book_id`)  AS books_count
 FROM book_author
GROUP BY book_author.`author_id`
HAVING   COUNT(book_author.`book_id`)=(SELECT MAX(t1.book_count) 
 FROM 
(SELECT COUNT(book_author.`book_id`)  AS book_count 
FROM  book_author
GROUP BY book_author.`author_id` )t1
)

answered Apr 28, 2022 at 20:11

 ***Example: 1***
 SELECT *
 FROM customer
 WHERE customer.ID IN
      (SELECT customer_id
         FROM (SELECT customer_id, MAX(cust_count)
                 FROM (SELECT customer_id,
                              COUNT(customer_id)
                                 AS cust_count
                         FROM `order`
                       GROUP BY customer_id) AS cust_count_tbl) AS cust_tbl);


***Example -2*** 

SELECT *
      FROM    customer
   LEFT JOIN
      (SELECT customer_id, COUNT(customer_id) AS cc
         FROM `order`
       GROUP BY customer_id
       ORDER BY cc DESC
        LIMIT 1) AS kk
   ON customer.ID = kk.customer_id
   WHERE kk.customer_id = customer.ID;

answered Feb 17, 2016 at 13:35

Источник

Последнее обновление 15 марта 2019 07:13:33 (UTC / GMT +8 часов)

script1adsense2code

script1adsense3code

MAX () с функцией подсчета

В этой части вы увидите использование SQL COUNT () вместе с SQL MAX ().

Пример:

Чтобы получить максимальное количество агентов в качестве псевдонима столбца «mycount» из таблицы «orders» со следующим условием:

1. ‘agent_code’ должен быть в группе,

можно использовать следующий оператор SQL:

SELECT MAX (mycount) 
FROM (SELECT agent_code,COUNT(agent_code) mycount 
FROM orders 
GROUP BY agent_code);

Пример таблицы: заказы

Выход:

 MAX (MYCOUNT)
------------
           7

Иллюстрированная презентация:

«SQL

SQL MAX () и COUNT () с HAVING

Чтобы получить данные «agent_code» и количество агентов для каждой группы «agent_code» из таблицы заказов со следующими условиями:

‘agent_code’ для группы будет равен результату внешнего запроса [SELECT MAX (agent_code) …….] со следующим условием —

внешний запрос выдает максимальное количество агентов, упомянутых как «mycount» из внутреннего запроса [SELECT agent_code,
COUNT (agent_code) mycount FROM orders GROUP BY agent_code] со следующим условием —

внутренний запрос выдает данные «agent_code» количество агентов в виде псевдонима столбца «mycount» из таблицы «orders» со следующим условием:

‘agent_code’ должен быть в группе,

можно использовать следующий оператор SQL:

SELECT agent_code, COUNT(agent_code) 
FROM orders  GROUP BY agent_code 
HAVING COUNT (agent_code)=( 
SELECT MAX(mycount) 
FROM ( 
SELECT agent_code, COUNT(agent_code) mycount 
FROM orders 
GROUP BY agent_code));

Пример таблицы: заказы

Выход:

 AGENT_CODE COUNT (AGENT_CODE)
---------- -----------------
A002 7

«SQL

Примечание. Выводы указанного оператора SQL, показанного здесь, взяты с использованием Oracle Database 10g Express Edition.

Вот слайд-презентация всех агрегатных функций.

Упражнения по SQL

Упражнения по SQL, практика, решение
SQL Получить данные из таблиц [33 Упражнения]
Булевы и реляционные операторы SQL [12 упражнений]
Подстановочные знаки SQL и специальные операторы [22 упражнения]
Агрегатные функции SQL [25 упражнений]
Вывод запроса форматирования SQL [10 упражнений]
SQL-запросы к нескольким таблицам [7 упражнений]
ФИЛЬТРАЦИЯ И СОРТИРОВКА в базе данных персонала [38 упражнений]
SQL СОЕДИНЯЕТ

SQL СОЕДИНЯЕТСЯ [29 упражнений]
SQL присоединяется к базе данных HR [27 упражнений]

SQL ПОДПИСИ

ПОДПИСИ SQL [39 упражнений]
SQL ПОДПИСИ по базе данных HR [55 упражнений]

SQL Union [9 упражнений]
SQL View [16 упражнений]
Управление учетными записями пользователей SQL [16 упражнение]
База данных фильмов

ОСНОВНЫЕ запросы к базе данных фильмов [10 упражнений]
ПОДПИСКИ на фильм База данных [16 упражнений]
ПРИСОЕДИНЯЕТСЯ к базе данных фильма [24 упражнения]

Футбольная база

Вступление
ОСНОВНЫЕ запросы по футболу базы данных [29 упражнений]
ПОДПИСКИ по футбольной базе данных [33 упражнения]

ПРИСОЕДИНЯЕТСЯ к запросам по футбольной базе данных [61 упражнений]

База данных больницы

Вступление
ОСНОВНЫЕ, ПОДПИСИ И СОЕДИНЕНИЯ [39 упражнений]

База данных сотрудников

ОСНОВНЫЕ запросы к базе данных сотрудников [115 упражнений]
БРОНИРОВАНИЕ на сотрудника База данных [77 Упражнения]

Еще не все!

Хотите улучшить вышеуказанную статью? Вносите свои заметки / комментарии / примеры через Disqus.

Предыдущая: Макс. Дата
Далее: Мин функция

Источник

Last update on August 19 2022 21:50:45 (UTC/GMT +8 hours)

MAX() with Count function

In this part, you will see the usage of SQL COUNT() along with the SQL MAX().

Example:

To get the maximum number of agents as column alias ‘mycount’ from the ‘orders’ table with the following condition —

1. ‘agent_code’ should be in a group,

the following SQL statement can be used :

SELECT MAX (mycount) 
FROM (SELECT agent_code,COUNT(agent_code) mycount 
FROM orders 
GROUP BY agent_code);

Sample table: orders

Output:

MAX(MYCOUNT)
------------
           7

Pictorial Presentation :

SQL MAX() and COUNT() with HAVING

To get data of ‘agent_code’, and number of agents for each group of ‘agent_code’ from the orders table with the following conditions —

‘agent_code’ for a group will be equal to the result of an outer query [SELECT MAX(agent_code)…….] with following condition —

the outer query produce the maximum number of agents mentioned as
‘mycount’ from an inner query [SELECT agent_code,
COUNT(agent_code) mycount FROM orders GROUP BY agent_code]
with following condition —

the inner query produced the data ‘agent_code’ number of agents as
column alias ‘mycount’ from the ‘orders’ table with the following
condition —

‘agent_code’ should be in a group,

the following SQL statement can be used :

SELECT agent_code, COUNT(agent_code) 
FROM orders  GROUP BY agent_code 
HAVING COUNT (agent_code)=( 
SELECT MAX(mycount) 
FROM ( 
SELECT agent_code, COUNT(agent_code) mycount 
FROM orders 
GROUP BY agent_code));

Sample table: orders

Output:

AGENT_CODE COUNT(AGENT_CODE)
---------- -----------------
A002                       7

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition

Here is a slide presentation of all aggregate functions.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Max Date
Next: Min function

SQL: Tips of the Day

Oracle find a constraint:

SQL> create table t23 (id number not null primary key)
  2  /

Table created.

SQL> select constraint_name, constraint_type
  2  from user_constraints
  3  where table_name = 'T23'
  4  /

CONSTRAINT_NAME                C
------------------------------ -
SYS_C00935190                  C
SYS_C00935191                  P

SQL>

Ref: https://bit.ly/3JORjm8

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us
at Facebook

Источник

Let’s say I have the following data in the Customers table: (nothing more)

ID   FirstName   LastName
-------------------------------
20   John        Mackenzie
21   Ted         Green
22   Marcy       Nate

What sort of SELECT statement can get me the number 22, in the ID column?

I need to do something like this to generate a unique ID. Sure I can let the system do this via auto-increment, but then how would I get the auto generated ID?

I thought of SELECT ID FROM Customers and counting the rows returned but this seems horribly inefficient, and in this case, it will incorrectly return «3», though I need a unique ID of 23.

Kevin

16.2k8 gold badges56 silver badges74 bronze badges

asked Oct 10, 2009 at 5:14

Robin RodricksRobin Rodricks

110k141 gold badges396 silver badges606 bronze badges

You can do

SELECT MAX(ID) FROM Customers;

answered Oct 10, 2009 at 5:15

If you’ve just inserted a record into the Customers table and you need the value of the recently populated ID field, you can use the SCOPE_IDENTITY function. This is only useful when the INSERT has occurred within the same scope as the call to SCOPE_IDENTITY.

INSERT INTO Customers(ID, FirstName, LastName)
Values
(23, 'Bob', 'Smith')

SET @mostRecentId = SCOPE_IDENTITY()

This may or may not be useful for you, but it’s a good technique to be aware of. It will also work with auto-generated columns.

Kevin

16.2k8 gold badges56 silver badges74 bronze badges

answered Oct 10, 2009 at 5:21

David AndresDavid Andres

31.3k7 gold badges45 silver badges36 bronze badges

SELECT * FROM Customers ORDER BY ID DESC LIMIT 1

Then get the ID.

McGarnagle

101k31 gold badges228 silver badges260 bronze badges

answered Oct 17, 2012 at 3:37

select max(id) from customers

warren

32.3k21 gold badges85 silver badges122 bronze badges

answered Oct 10, 2009 at 5:15

deostrolldeostroll

11.6k21 gold badges87 silver badges158 bronze badges

To get it at any time, you can do SELECT MAX(Id) FROM Customers .

In the procedure you add it in, however, you can also make use of SCOPE_IDENTITY — to get the id last added by that procedure.
This is safer, because it will guarantee you get your Id—just in case others are being added to the database at the same time.

Pritesh Jain

9,1064 gold badges37 silver badges51 bronze badges

answered Oct 10, 2009 at 5:19

BrisbeBrisbe

1,5782 gold badges20 silver badges42 bronze badges

If you’re talking MS SQL, here’s the most efficient way. This retrieves the current identity seed from a table based on whatever column is the identity.

select IDENT_CURRENT('TableName') as LastIdentity

Using MAX(id) is more generic, but for example I have an table with 400 million rows that takes 2 minutes to get the MAX(id). IDENT_CURRENT is nearly instantaneous…

answered Oct 10, 2009 at 17:34

DamonDamon

6873 silver badges17 bronze badges

select max(id) from Customers

warren

32.3k21 gold badges85 silver badges122 bronze badges

answered Oct 10, 2009 at 5:16

monksymonksy

14.1k17 gold badges75 silver badges123 bronze badges

If you are using AUTOINCREMENT, use:

SELECT LAST_INSERT_ID();

Assumming that you are using Mysql:
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Postgres handles this similarly via the currval(sequence_name) function.

Note that using MAX(ID) is not safe, unless you lock the table, since it’s possible (in a simplified case) to have another insert that occurs before you call MAX(ID) and you lose the id of the first insert. The functions above are session based so if another session inserts you still get the ID that you inserted.

ItsJ0el

551 silver badge5 bronze badges

answered Oct 10, 2009 at 5:33

If you’re not using auto-incrementing fields, you can achieve a similar result with something like the following:

insert into Customers (ID, FirstName, LastName)
    select max(ID)+1, 'Barack', 'Obama' from Customers;

This will ensure there’s no chance of a race condition which could be caused by someone else inserting into the table between your extraction of the maximum ID and your insertion of the new record.

This is using standard SQL, there are no doubt better ways to achieve it with specific DBMS’ but they’re not necessarily portable (something we take very seriously in our shop).

answered Oct 10, 2009 at 6:53

paxdiablopaxdiablo

847k233 gold badges1568 silver badges1943 bronze badges

You can also use relational algebra. A bit lengthy procedure, but here it is just to understand how MAX() works:

E := π_ID (Table_Name)
E1 := π_ID (σ_{ID >= ID’} ((ρ_ID’ E) ⋈ E)) – π_ID (σ_{ID < ID’} ((ρ_ID’ E) ⋈ E))

Your answer:
Table_Name ⋈ E1

Basically what you do is subtract set of ordered relation(a,b) in which a<b from A where a, b ∈ A.

For relation algebra symbols see:
Relational algebra
From Wikipedia

answered Jan 24, 2013 at 17:33

ArnabArnab

5551 gold badge7 silver badges12 bronze badges

Here’s how I would make the next ID:

INSERT INTO table_name (
            ID,
            FIRSTNAME,
            SURNAME) 
            VALUES ((( 
                    SELECT COALESCE(MAX(B.ID)+1,1) AS NEXTID 
                        FROM table_name B
                        )), John2, Smith2);

With this you can make sure that even if the table ID is NULL, it will still work perfectly.

answered Jan 12, 2021 at 21:20

Depends on what SQL implementation you are using. Both MySQL and SQLite, for example, have ways to get last insert id. In fact, if you’re using PHP, there’s even a nifty function for exactly that mysql_insert_id().

You should probably look to use this MySQL feature instead of looking at all the rows just to get the biggest insert ID. If your table gets big, that could become very inefficient.

answered Oct 10, 2009 at 6:26

sohumsohum

3,2072 gold badges39 silver badges62 bronze badges

If you want to just select the id use select max(id) from customer.

If you want to select the entire row then use a query like this:

select c1.*
from customer c1, (select max(id) as max_id from customer )c2 
where c1.id=c2.max_id

c2 is an alias for the new temporary table which contains max id. Then its cross product is taken with customer table to get the entire row.

Here we are writing a query in the from clause, which can often be quite useful.

answered Jul 7, 2018 at 19:48

To find the next (still not used) auto-increment, I am using this function for somewhat years now.

public function getNewAI($table)
    {
        $newAI = false;

        $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
        if(mysqli_connect_errno()) {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }

        $sql = "SHOW TABLE STATUS LIKE '".$table."'";

        $result = $mysqli->query($sql);

        if($result) {
            $row = $result->fetch_assoc();

            $newAI = $row['Auto_increment'];
        }
        $mysqli->close();

        return $newAI;
    }

answered Dec 4, 2019 at 14:55

select * from tablename order by ID DESC

that will give you row with id 22

Ben

51.5k36 gold badges127 silver badges148 bronze badges

answered Aug 23, 2012 at 6:02

In PHP:

$sql = mysql_query("select id from customers order by id desc");
$rs = mysql_fetch_array($sql);
if ( !$rs ) { $newid = 1; } else { $newid = $rs[newid]+1; }

thus $newid = 23 if last record in column id was 22.

skuntsel

11.6k11 gold badges44 silver badges67 bronze badges

answered Feb 13, 2015 at 13:27

Источник

This SQL tutorial explains how to use the SQL MAX function with syntax and examples.

Description

The SQL MAX function is used to return the maximum value of an expression in a SELECT statement.

Syntax

The syntax for the MAX function in SQL is:

SELECT MAX(aggregate_expression)
FROM tables
[WHERE conditions];

OR the syntax for the MAX function when grouping the results by one or more columns is:

SELECT expression1, expression2, ... expression_n,
       MAX(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Parameters or Arguments

expression1, expression2, … expression_n: Expressions that are not encapsulated within the MAX function and must be included in the GROUP BY clause at the end of the SQL statement.
aggregate_expression: This is the column or expression from which the maximum value will be returned.
tables: The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions: Optional. These are conditions that must be met for the records to be selected.

Example — With Single Expression

The simplest way to use the SQL MAX function would be to return a single field that calculates the MAX value.

For example, you might wish to know the maximum salary of all employees.

SELECT MAX(salary) AS "Highest salary"
FROM employees;

In this SQL MAX function example, we’ve aliased the MAX(salary) field as «Highest salary». As a result, «Highest salary» will display as the field name when the result set is returned.

Example — Using SQL GROUP BY Clause

In some cases, you will be required to use the SQL GROUP BY clause with the SQL MAX function.

For example, you could also use the SQL MAX function to return the name of each department and the maximum salary in the department.

SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department;

Because you have listed one column in your SQL SELECT statement that is not encapsulated in the MAX function, you must use the SQL GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

Frequently Asked Questions

Question: I’m trying to pull some info out of a table. To simplify, let’s say the table (report_history) has 4 columns: user_name, report_job_id, report_name, and report_run_date.

Each time a report is run in Oracle, a record is written to this table noting the above info. What I am trying to do is pull from this table when the last time each distinct report was run and who ran it last.

My initial query:

SELECT report_name, MAX(report_run_date)
FROM report_history
GROUP BY report_name

runs fine. However, it does not provide the name of the user who ran the report.

Adding user_name to both the select list and to the group by clause returns multiple lines for each report; the results show the last time each person ran each report in question. (i.e. User1 ran Report 1 on 01-JUL-03, User2 ran Report1 on 01-AUG-03). I don’t want that….I just want to know who ran a particular report the last time it was run.

Any suggestions?

Answer: This is where things get a bit complicated. The SQL SELECT statement below will return the results that you want:

SELECT rh.user_name, rh.report_name, rh.report_run_date
FROM report_history rh,
  (SELECT MAX(report_run_date) AS maxdate, report_name
   FROM report_history
   GROUP BY report_name) maxresults
WHERE rh.report_name = maxresults.report_name
AND rh.report_run_date= maxresults.maxdate;

Let’s take a few moments to explain what we’ve done.

First, we’ve aliased the first instance of the report_history table as rh.

Second, we’ve included two components in our FROM clause. The first is the table called report_history (aliased as rh). The second is a select statement:

(SELECT MAX(report_run_date) AS maxdate, report_name
 FROM report_history
 GROUP BY report_name) maxresults

We’ve aliased the max(report_run_date) as maxdate and we’ve aliased the entire result set as maxresults.

Now, that we’ve created this select statement within our FROM clause, Oracle will let us join these results against our original report_history table. So we’ve joined the report_name and report_run_date fields between the tables called rh and maxresults. This allows us to retrieve the report_name, max(report_run_date) as well as the user_name.

Question: I need help with a SQL query. I have a table in Oracle called orders which has the following fields: order_no, customer, and amount.

I need a query that will return the customer who has ordered the highest total amount.

Answer: The following SQL should return the customer with the highest total amount in the orders table.

SELECT query1.*
FROM (SELECT customer, SUM(orders.amount) AS total_amt
      FROM orders
      GROUP BY orders.customer) query1,

     (SELECT MAX(query2.total_amt) AS highest_amt
      FROM (SELECT customer, SUM(orders.amount) AS total_amt
            FROM orders
            GROUP BY orders.customer) query2) query3
WHERE query1.total_amt = query3.highest_amt;

This SQL SELECT statement will summarize the total orders for each customer and then return the customer with the highest total orders. This syntax is optimized for Oracle and may not work for other database technologies.

Question: I’m trying to retrieve some info from an Oracle database. I’ve got a table named Scoring with two fields — Name and Score. What I want to get is the highest score from the table and the name of the player.

Answer: The following SQL SELECT statement should work:

SELECT Name, Score
FROM Scoring
WHERE Score = (SELECT MAX(Score) FROM Scoring);

Question: I need help in a SQL query. I have a table in Oracle called cust_order which has the following fields: OrderNo, Customer_id, Order_Date, and Amount.

I would like to find the customer_id, who has Highest order count.

I tried with following query.

SELECT MAX(COUNT(*))
FROM CUST_ORDER
GROUP BY CUSTOMER_ID;

This gives me the max Count, But, I can’t get the CUSTOMER_ID. Can you help me please?

Answer: The following SQL SELECT statement should return the customer with the highest order count in the cust_order table.

SELECT query1.*
FROM (SELECT Customer_id, Count(*) AS order_count
      FROM cust_order
      GROUP BY cust_order.Customer_id) query1,

     (SELECT max(query2.order_count) AS highest_count
      FROM (SELECT Customer_id, Count(*) AS order_count
            FROM cust_order
            GROUP BY cust_order.Customer_id) query2) query3
WHERE query1.order_count = query3.highest_count;

This SQL SELECT statement will summarize the total orders for each customer and then return the customer with the highest order count. This syntax is optimized for Oracle and may not work for other database technologies.

Question: I’m trying to get the employee with the maximum salary from department 30, but I need to display the employee’s full information. I’ve tried the following query, but it returns the result from both department 30 and 80:

SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary)
                FROM employees
                WHERE department_id=30);

Answer: The SQL SELECT statement that you have written will first determine the maximum salary for department 30, but then you select all employees that have this salary. In your case, you must have 2 employees (one in department 30 and another in department 80) that have this same salary. You need to make sure that you are refining your query results to only return employees from department 30.

Try using this SQL SELECT statement:

SELECT *
FROM employees
WHERE department_id=30
AND salary = (SELECT MAX(salary)
              FROM employees
              WHERE department_id=30);

This will return the employee information for only the employee in department 30 that has the highest salary.

Источник