SQL MAX() Function

The SQL MAX() is a function, and return the largest or highest value of a numeric table column from query result.

The SQL MAX() function is supports only numeric column.

It can be used in SQL SELECT statement as well in SQL WHERE clause.

Example 1: Using the following query, you can get the two columns SuperVisor and totalcontracts:

select SuperVisor,
count(ContractNo) as totalcontracts
from Contract
group by SuperVisor

I now want to use a select statement to extract the most total contracts possible from the data that was retrieved.

It is simple to get the greatest amount. Simply create a new MAX query and use the resulting table from your first COUNT query:

select max(totalcontracts) as highest_total
  from (
 select SuperVisor                               
 , count(ContractNo) as totalcontracts       
 from Contract                                    
 group 
 by SuperVisor                              
) as t

Remember that you can use a query wherever a table can be used as long as you follow a few basic syntactical conventions, including enclosing it in parentheses and designating a table alias.

Example 2: But what if you want to know which supervisor achieved this highest total count:

There's a hard way and an easy way. The hard way is like this:

select SuperVisor
  , count(ContractNo) as totalcontracts
  from Contract
group
  by SuperVisor
having count(ContractNo) =
  ( select max(totalcontracts) as highest_total
  from (
  select SuperVisor
  , count(ContractNo) as totalcontracts
  from Contract
  group
  by SuperVisor
 ) as t
)

The simplest method is to use TOP, LIMIT, or a similar technique to sort the results of the MAX query in decreasing order by the total number of contracts. The hard technique above handles ties correctly; keep in mind that this would not be conventional SQL and that you must take care of ties yourself.

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

To get the maximum 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 :

Sample table: orders

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

Output:

The aggregate function MAX calculates an expression's maximum value across a collection of rows (see Aggregates (set functions)). Only expressions that evaluate to built-in data types are permitted to use MAX (including CHAR, VARCHAR, DATE, TIME, CHAR FOR BIT DATA, etc.).

Syntax:

MAX ( [ DISTINCT | ALL ] Expression )

Returns expr's largest or highest value. In that scenario, MAX() returns the largest string value. MAX() can also take a string argument. The greatest distinct value of expr can be found using the DISTINCT keyword, but doing so yields the same outcome as leaving the keyword out. Per SelectExpression, only one DISTINCT aggregate expression is permitted.

MAX() may yield a different top result than ORDER BY DESC because SET and ENUM fields are currently compared by their string value rather than their relative position in the set.

Example 1: The following query is not allowed:

SELECT COUNT (DISTINCT flying_time), MAX (DISTINCT miles)
FROM Flights

The Expression is not permitted to contain another aggregate or subquery, however it may have multiple column references or expressions. It needs to evaluate to an inherent data type. Therefore, procedures that evaluate to built-in data types can be called. (For instance, the method int or java.lang.Integer returns an INTEGER.) The aggregate ignores over a value in an expression that evaluates to NULL.

The maximum value is determined by the comparison criteria for the type. The amount of whitespace at the end of the value for CHAR and VARCHAR can influence how MAX is calculated. Because blank spaces are ignored for character comparisons, if the values "z" and "z" are both placed in a column, you are unable to determine which one will be returned as the maximum.

The resulting data type is the same as the expression on which it operates (it will never overflow).

Example 2: Find the latest date in the FlightAvailability table:

SELECT MAX (flight_date) FROM FlightAvailability

Find the longest flight originating from each airport, but only when the longest flight is over 10 hours:

SELECT MAX(flying_time), orig_airport
FROM Flights
GROUP BY orig_airport
HAVING MAX(flying_time) > 10

Example 3:

Step 1: Create Table

CREATE TABLE cars
    (
      MAKER  VARCHAR (25),
      MODEL  VARCHAR (25),
      PRICE  NUMERIC
);

Step 2: Insert Data

INSERT INTO CARS VALUES('CHRYSLER','CROSSFIRE',33620);
INSERT INTO CARS VALUES('CHRYSLER','300M',29185);
INSERT INTO CARS VALUES('HONDA','CIVIC',15610);
INSERT INTO CARS VALUES('HONDA','ACCORD',19300);
INSERT INTO CARS VALUES('FORD','MUSTANG',15610);
INSERT INTO CARS VALUES('FORD','LATESTnGREATEST',NULL);
INSERT INTO CARS VALUES('FORD','FOCUS',13005);

Step 3: Display records

SELECT
MAX(price) max_price
FROM cars;

Output:

Step 4: Drop table:

drop table cars;

You can use SQL HAVING CLAUSE in conjunction with SQL MAX() to determine a column's maximum value across all groups. The aggregate function is only permitted to use the SQL HAVING CLAUSE.

Example 1: When we use the groupby class:

For instance, the SELECT query below will return the maximum value next to each value for the birthdate and salary fields for all employees, allowing you to compare each number with the maximum value as seen below:

SELECT TOP 10 ID, EmpName, EmpDateOfBirth, 
    YoungEmp = (SELECT MAX(EmpDateOfBirth) FROM MAXDemo), EmpSalary, 
    LargeSalary = (SELECT MAX(EmpSalary) FROM MAXDemo )
FROM MAXDemo
WHERE EmpIsActive = 1
GROUP BY ID, EmpName, EmpDateOfBirth , EmpSalary
ORDER BY EmpSalary DESC

Example 2: To get data of 'cust_city', 'cust_country' and maximum 'outstanding_amt' from the customer table with following conditions:

Sample table :customer

To combination of 'cust_country' and 'cust_city' should make a group' the following SQL statement can be used :

SELECT cust_city, cust_country, MAX(outstanding_amt) 
FROM customer 
GROUP BY cust_country, cust_city 
HAVING MAX(outstanding_amt)>10000;

Output :

CUST_CITY                           CUST_COUNTRY         MAX(OUTSTANDING_AMT)
----------------------------------- -------------------- --------------------
Bangalore                           India                               12000
Chennai                             India                               11000
London                              UK                                  11000
Mumbai                              India                               12000
Torento                             Canada                              11000

Example 3: For example, the following query selects only the highest payment paid by each customer and the payments are greater than 8.99.

SELECT customer_id,
	MAX (amount)
FROM
	payment
GROUP BY
	customer_id
HAVING MAX(amount) > 8.99

Example 1: Find the second highest salary using max.

Step 1 - Create a Database

The following script should be run in your SQL Server to build the "chittadb" database.

Chittadb database creation

To run the aforementioned script, first choose the query and then hit F5 or click the Execute button.

You should see a message, “Command(s) completed successfully.” This means your new database has been created.

Step 2 - Create a table

Open your SQL Server and use the following script to create a table “tbl_Employees”.

Create table tbl_Employees  
(  
     Id int primary key not null identity(1,1),  
     FirstName varchar(50),  
     LastName varchar(20),  
     Location varchar(20),  
     Gender varchar(50),  
     Salary int  
)  

Execute the above query to create “tbl_Employees “.

You should see a message, “Command(s) completed successfully.”

Step 3 - Insert Data

Now, the data has been inserted into the table.

Insert into tbl_Employees values ('Chittaranjan', 'Swain','Odisha', 'Male', 80000)  
Insert into tbl_Employees values ('Chandin', 'Swain', 'Pune','Female', 76000)  
Insert into tbl_Employees values ('Mitu', 'Pradhan','Delhi', 'Male', 55000)  
Insert into tbl_Employees values ('Jeni', 'Swain','Chennai', 'Female', 76000)  
Insert into tbl_Employees values ('Adyashree', 'Swain','UK', 'Female', 49000)  
Insert into tbl_Employees values ('Ram', 'Kumar','US', 'Male', 39000)  
Insert into tbl_Employees values ('Jitendra', 'Gouad','Hydrabad', 'Male', 35000)  
Insert into tbl_Employees values ('Dibas', 'Hembram','Bangalore', 'Male', 55000)  

Execute the above query, you should see a message, “Command(s) completed successfully.”

Step 4 - Display Records

Now retrieve all data from the “tbl_Employees” table.

select * from tbl_Employees  

Step 5: Use a Subquery and the Max() function to obtain the second-highest salary:

Select Max(Salary) as Salary from tbl_Employees 
where Salary <(select MAX(Salary) from tbl_Employees)  

Output:

Example 2: To retrieve the second-highest salary from the Employee table, use a SQL query.

For instance, the query should return 200 as the second-highest wage for the Employee table mentioned above. The query should return null if there is no second-highest income.

SELECT 
    MAX(salary) as SecondHighestSalary
FROM Employee
WHERE salary < (SELECT
 MAX(salary)
 FROM Employee)

We first choose the highest income, which will give us 300, in the subquery. Then, using the WHERE clause in the outer query, we are once more choosing the highest income that is less than 300, giving us 200, the second-highest salary in this table.

Example 3: How to Find Second Highest Salary in SQL:

Without a question, data is significant in today's environment. However, to effectively manage it, you must become an expert in data management. That leads us to SQL, also known as Structured Query Language, the language that forms the basis for data administration. Relational databases, which are commonly used in enterprises, are primarily written in SQL.

Here is the procedure of how a SQL command is executed:

• Any RDBMS selects the optimal approach to carry out a SQL command, and the SQL engine determines how to interpret the request when it is executed.
• The technique makes use of several elements. These parts include things like the optimization engine, query engine, query dispatcher, classic, and so on.
• All non-SQL queries are handled by the traditional query engine, however logical files are not supported by the SQL query engine.
• Post-Graduate Business Analysis Program.

In partnership with Purdue UniversityVIEW COURSEPost Graduate Program in Business Analysis

How to Write a Query?

Now, let’s take a look at how you can write an SQL query.

Understand the Process

As soon as a query is input, it is parsed into a tree. It is easier to determine whether a query satisfies the syntactical and semantic requirements, or is recognizable, with the help of a parser, or computer software that translates SQL instructions into a parse tree.

If everything is in order, the parser then creates an internal query that is subsequently sent to the rewrite engine.

The task optimizer then analyzes how many execution plans it has and chooses the best one for your query. This program serves as a representation of the algorithm that will be utilized to carry out the command.

Getting Database Ready

A database must initially be launched in order to create the environment necessary for writing a SQL query.

The "CREATE DATABASE" line can be used to create a database if your DBMS software is already downloaded.

Syntax for creating a database:

CREATE DATABASE   database_name;

Creating a Table

In case the database is prepared, you may now move on to creating a table. As was already said, tables help you organize your searches and make data easier to handle.

The ‘CREATE TABLE' statement is required to create a table.

Syntax for Creating a Table in SQL:

CREATE TABLE table_name (
column1_name_constraint,
…
)

Starting With Basic SQL Statements

Once the table is ready and operational, you can now start writing SQL queries by putting data into the table.

Here are some fundamental SQL statements to assist you effectively enter data into the table and maintain it.

1. INSERT Statement

This statement is used to initialize a table with new rows. In this case, the syntax would be as follows:

INSERT (column 1, column 2, column 3) INTO table name;

2. SELECT Clause

Using this clause, you can extract data from one or more tables. The following is the syntax for this clause:

FROM table name, SELECT column1 name, column2 name

3. WHERE Clause

By including this phrase in a table, you can choose data based on a criterion. It can be helpful if you want to change or remove all references to a record in a row or column depending on a condition like a name, birthdate, or other specifics.

The syntax for WHERE clause is as follows:

SELECT column1_name FROM table_name WHERE condition

4. UPDATE statement

The UPDATE command can be used to update the data that has previously been inserted and selected.

The same's syntax is shown below.

UPDATE table_name
SET column1_name = value 1…
WHERE condition1, condition2…

There are many more commands and statements in SQL, but for now we'll learn how to find the second highest salary in SQL.

How to Write an SQL Query to Find the Second Highest Salary?

We will execute the above problem in 3 steps to find the second highest salary in SQL, that are:

• Creating a table
• Inserting values in table
• Writing query to find second highest salary
• Now, let’s look at each of the above steps in detail.

Step 1: Creating Table

The first step is to create a table. Here, we’ll create a table named as Employee having attributes ID, NAME and SALARY.

SQL query for creating the schema for our table is given below.

CREATE TABLE Employee ( 
ID INTEGER, NAME VARCHAR(20), 
SALARY INTEGER 
)

Now that we have the table's schema, let's proceed to the next phase, where we will add some values to the schema in order to achieve the desired outcome.

Step 2: Inserting Values in Table

In this step, we will insert some sample records into the table.

INSERT INTO Employee VALUES(1,'Mandy',12000);
INSERT INTO Employee VALUES(2,'Chris',15000);
INSERT INTO Employee VALUES(3,'Henry',10000);
INSERT INTO Employee VALUES(4,'Katy',10000);
INSERT INTO Employee values(5,'Adams',11000);

Step 3:

We will use the idea of a subquery to find the second highest salary in the aforementioned table. To do this, we will first identify the highest pay in the table, and then we will nest that query into a subquery to find the second highest salary in SQL.

Write the following query to determine the table's highest income.

SELECT MAX(SALARY) FROM Employee;

Output:

We now nest the aforementioned question within another query, as written below, to determine the second-highest income.

SELECT MAX(SALARY) FROM Employee WHERE SALARY < (SELECT MAX(SALARY) FROM Employee);

Output:

Example 1: I'm trying to insert a new entry and use max()+1 to set the customer_id. This is due to the fact that the table already has an auto_increment on a different column named id and that several rows in the table will share the same customer_id.

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  ((SELECT MAX( customer_id ) FROM customers) +1, 'jim', 'sock')

I keep getting the following error:

Let's look at how I would prevent two different customers from being added at the same time and not having the same customer id. You cannot provide target table "customers" for update in FROM clause.

True, a single query cannot alter and select from the same table. The aforementioned would require two different inquiries.

Using a transaction is the best option, but if you're not using innodb tables, locking the tables first, then running your queries, is the second best option.

Lock tables customers write;

$max = SELECT MAX( customer_id ) FROM customers;

Grab the max id and then perform the insert:

INSERT INTO customers( customer_id, firstname, surname )
VALUES ($max+1 , 'jim', 'sock')
unlock tables;

Example 2: I am working with the following Knowledge Tree table in MySQL - it holds reference data.

Step 1: Create Table

CREATE TABLE `document_types_lookup` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(100),
    `disabled` BIT DEFAULT'0' NOT NULL
);

Step 2: Alter Table

ALTER TABLE `document_types_lookup` ADD PRIMARY KEY (`id`);

Step 3:

I had to insert thousands of rows, but I needed SQL that could increment the ID because there was no auto-increment field for the ID. SQL only, no scripts needed. I found a bug report (allowing INSERT ... SELECT MAX (x) +1 from the same table) that contains the following snippet that increments the ID of the insert using select (max (id) +1).

Insert into foo (lfd) select (max (lfd) +1) from foo;

And I was able to make this statement work myself (just insert the ID into the record), but the rest of the record values Could not be adjusted to add.

insert into
   document_types_lookup(id, name, disabled)
values
   (select (max(id)+1) from document_types_lookup, 'a name', false);

I always got the below error.

insert into document_types_lookup(id, name, disabled) 
values (select (max(id)+1) from document_types_lookup, 'a name', false)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select (max(id)+1) from document_types_lookup, 'a name', false)' at line 1

So, my work-around is to use two statements instead of one: an insert followed by an update, as below.

insert into
document_types_lookup(id)
select (max(id)+1) from document_types_lookup;

Step 4: Update Table

update
   document_types_lookup
set
   name = 'CORP_HMN_000_CA_',
   disabled = false
where
   name is null;

Any issues with this script, please? You should since there are two key reasons why this script is risky. First off, it isn't thread safe, meaning using select (max(id)+1) in an insert can be risky if several individuals are simultaneously updating the table. You can't be sure that a new record won't have been added by someone else between the time you choose the max ID + 1 and when the insert actually occurs, making your max ID + 1 no longer unique when your insert statement eventually executes. To fix this, lock the table before inserting data and unlock it afterward. The second reason that this is a dangerous script is because it assumes there will only ever be one record where name is null; it does this as I could think of no other way to determine what record I just inserted.

So, this script works only because a) I knew I was the only person who would be updating the table at that time and b) I knew name would have a value for all records other than the one I had just inserted.

Example 1: How to select the first row per group:

You've used GROUP BY to category your data and want to show just the first row from each group.

Our database has the following data in a table called exam_results:

first_name	last_name	year	result
John	         Klein	        2020	40
Edith	         Black	        2020	43
Mark	         Johnson	2019	32
Laura	         Summer	        2020	35
Kate	         Smith	        2019	41
Jacob	         Black	        2019	44
Tom	         Bennett	2020	38
Emily	         Kelly	        2020	43

Find the student who performed the best for each year. If two students are tied for first place in a class, we will choose a random student to display.

WITH added_row_number AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY year ORDER BY result DESC) AS row_number
  FROM exam_results
)
SELECT
  *
FROM added_row_number
WHERE row_number = 1;

Result:

first_name	last_name	year	result	row_number
Jacob	          Black	        2019	  44	    1
Emily	          Kelly	        2020	  43	    1

Discussion: You must first create a CTE in which each row inside each group is given a number. You can use the ROW_NUMBER() function to accomplish that. You can define the order in which the numbers should be given to the rows in OVER() as well as the groups into which the rows should be separated (PARTITION BY) (ORDER BY).

Example 2: Take a look at the result of the inner query:

SELECT *,
ROW_NUMBER() OVER(PARTITION BY year ORDER BY result DESC) AS row_number
FROM exam_results;

Output:

first_name	last_name	year	result	row_number
Jacob	          Black	        2019	  44	    1
Kate	          Smith	        2019	  41	    2
Mark	          Johnson	2019	  32	    3
Emily	          Kelly	        2020	  43	    1
Edith	          Black	        2020	  43	    2
John	          Klein	        2020	  40	    3
Tom	          Bennett	2020	  38	    4
Laura	          Summer	2020	  35	    5

Each group's row numbers are assigned by you (i.e., year). Based on the value of the result column, each row has a unique row number. The DESC keyword following the ORDER BY result causes the rows to be sorted in descending order. The rows are assigned separate numbers even if more than one row in a group has the same value of the result. However, Emily Kelly and Edith Black have distinct row numbers while having the same outcome. Use RANK() or DENSE_RANK() in place of ROW_NUMBER() to alter this behaviour and assign the same row number for the same result within a group.

Example 3: You choose all the data from the CTE (added_row_number) in the outer query and use a WHERE condition to identify which row from each group to display. The condition is row_number = 1 because we want to display the first row in this instance.

Be aware that you can simply change the answer to obtain, for instance, the second row of each group.

WITH added_row_number AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY year ORDER BY result DESC) AS row_number
  FROM exam_results
)
SELECT
  *
FROM added_row_number
WHERE row_number = 2;

Output:

first_name	last_name	year	result	row_number
Kate	          Smith	        2019	  41	    2
Edith	          Black	        2020	  43	    2

Example 4: If you want to get the row(s) with the second highest value of result within each group, you should use the DENSE_RANK() function. While the ROW_NUMBER() function creates consecutive numbers for each row in a group, resulting in different values assigned to the rows with the same result, the DENSE_RANK() function gives the same number to the rows with the same result.

WITH added_dense_rank AS (
  SELECT
    *,
    DENSE_RANK() OVER(PARTITION BY year ORDER BY result DESC) AS rank
  FROM exam_results
)
SELECT
  *
FROM added_dense_rank
WHERE rank = 2;

Result:

first_name	last_name	year	result	rank
Kate	          Smith	        2019	  41	 2
John	          Klein	        2020	  40	 2

John Klein has the second-highest outcome value (40) for the year 2020, as you can see. John Klein is the third student in the group, yet his score is the same as the first two, who both have a rank = 1.

Example 5: Here are some typical SQL issues and their corresponding fixes: How can I locate the most recent program-specific log entry? How can I identify the most well-liked product inside each category? How can I determine each player's highest score? These "choose the extreme from each group" questions may typically be resolved using the same methods. In this article, I'll go over how to do that, including the trickier issue of choosing the top N entries rather of simply the top 1.

This subject is connected to my most recent article on numbering rows (see my articles about MySQL-specific and generic techniques to assign a number to each row in a group). With the addition of a price column, I'll therefore utilize much the same table and data as I did in earlier articles:

+--------+------------+-------+
| type   | variety    | price |
+--------+------------+-------+
| apple  | gala       |  2.79 | 
| apple  | fuji       |  0.24 | 
| apple  | limbertwig |  2.87 | 
| orange | valencia   |  3.59 | 
| orange | navel      |  9.36 | 
| pear   | bradford   |  6.05 | 
| pear   | bartlett   |  2.14 | 
| cherry | bing       |  2.55 | 
| cherry | chelan     |  6.33 | 
+--------+------------+-------+

Selecting the one maximum row from each group:

Imagine that I want to choose the most current log entries for every application, the most recent modifications to an audit database, or anything similar. This query is frequently asked in mailing lists and IRC channels. I'll reword the query using the analogy of apples. I want to choose the least expensive fruit from each variety. Here’s the desired result:

+--------+----------+-------+
| type   | variety  | price |
+--------+----------+-------+
| apple  | fuji     |  0.24 | 
| orange | valencia |  3.59 | 
| pear   | bartlett |  2.14 | 
| cherry | bing     |  2.55 | 
+--------+----------+-------+

There are a couple standard fixes for this issue. Finding the desired price value and then choosing the rest of the row based on that require two steps in each case.

A so-called self-join is one popular remedy. The first step is to arrange the fruits according to type (apple, cherry, etc.) and select the lowest price:

select type, min(price) as minprice
from fruits
group by type;

Result:

+--------+----------+
| type   | minprice |
+--------+----------+
| apple  |     0.24 | 
| cherry |     2.55 | 
| orange |     3.59 | 
| pear   |     2.14 | 
+--------+----------+

The second step is to choose the remaining rows by linking these outcomes back to the original table. The first query must be transformed into a subquery because it is grouped in order to be joined to the table that is not grouped:

select f.type, f.variety, f.price
from (
   select type, min(price) as minprice
   from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;

Result:

+--------+----------+-------+
| type   | variety  | price |
+--------+----------+-------+
| apple  | fuji     |  0.24 | 
| cherry | bing     |  2.55 | 
| orange | valencia |  3.59 | 
| pear   | bartlett |  2.14 | 
+--------+----------+-------+

With a connected subquery, this is a typical alternative method. Depending on how effective the query optimizer is on your system, this may be significantly less effective. But maybe it makes more sense to you now.

select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type);

Result:

+--------+----------+-------+
| type   | variety  | price |
+--------+----------+-------+
| apple  | fuji     |  0.24 | 
| orange | valencia |  3.59 | 
| pear   | bartlett |  2.14 | 
| cherry | bing     |  2.55 | 
+--------+----------+-------+

Both queries are logically equivalent, though they may not perform the same.

(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2)
union all
(select * from fruits where type = 'cherry' order by price limit 2)

I won't go into too much information about this method because Peter Zaitsev has written extensively about it. It might be a great option if it serves your needs.

One note: use UNION ALL, not just UNION. This stops the server from sorting the returned results to get rid of duplicates. Since there won't be any duplicates in this situation, I'm directing the server to avoid that (expensive, pointless) step.

Example 1: SQL Server - Max columns per primary key:

The maximum limit for primary key column list is 16.

CREATE DATABASE db_MaxColumnPer  
Go  
  
USE [db_MaxColumnPer]  
/*=================================
Create 16 columns per primary key
===================================*/
CREATE TABLE MaxColumnPerPrimaryKey16(  
[ID1] [int],  
[ID2] [int],  
[ID3] [int],  
[ID4] [int],  
[ID5] [int],  
[ID6] [int],  
[ID7] [int],  
[ID8] [int],  
[ID9] [int],  
[ID10] [int],  
[ID11] [int],  
[ID12] [int],  
[ID13] [int],  
[ID14] [int],  
[ID15] [int],  
[ID16] [int],  
[ID17] [int],  
[ID18] [int]   
CONSTRAINT PK_MaxColumnPerPrimaryKey   
PRIMARY KEY(ID1,ID2,ID3,ID4,ID5,ID6,ID7,ID8,ID9,ID10,ID11,ID12,ID13,ID14,ID15,ID16)   
)   
GO  

Result :

We can create one primary key for 1-16 columns in a table.

If we create a primary key more than 16 columns in a table SQL server returns error.

/*=================================
Create 17 or more columns per primary key
Following error will be occur
===================================*/

CREATE TABLE MaxColumnPerPrimaryKey17(  
[ID1] [int],  
[ID2] [int],  
[ID3] [int],  
[ID4] [int],  
[ID5] [int],  
[ID6] [int],  
[ID7] [int],  
[ID8] [int],  
[ID9] [int],  
[ID10] [int],  
[ID11] [int],  
[ID12] [int],  
[ID13] [int],  
[ID14] [int],  
[ID15] [int],  
[ID16] [int],  
[ID17] [int],  
[ID18] [int]   
CONSTRAINT PK_MaxColumnPerPrimaryKey   
PRIMARY KEY (ID1,ID2,ID3,ID4,ID5,ID6,ID7,ID8,ID9,ID10,ID11,ID12,ID13,ID14,ID15,ID16,ID17)   
)  
GO  

Error:

Summary: In this blog, we've seen that an error happens when a primary key in SQL Server exceeds its limit.

Example 2: The exact syntax will differ depending on which DBMS you are using. In sql server for keys of one letter followed by five digits assuming you want to keep the leading zeros:

This is T-SQL, so Sql Server. Substring or an equivalent is waht you are looking for.

Select Max(SubString(id,2,5)) From t

t-sql isn't chock full of string functions but there's a fair few, and you can usually glue a few together to get what you want.

Example 3:

select max(id)
from t

If the RDBMS does not support max() then:

select id
from t
order by id desc
limit 1

The MAX and MIN functions do not by default include NULL in their assessment of your data. If there is a NULL date in a column that only contains dates, for example, MAX and MIN will both disregard that value.

Example 1: For instance, take the following data set as an example.

WorkID    StoreID       	EndDate
———– 	   ———–		 	———————–
1              50              NULL
2              10             1900-01-01 00:00:00.000
3              20             1925-01-01 00:00:00.000
4              30             1950-01-01 00:00:00.000
5              40             1975-01-01 00:00:00.000
6              19             2010-01-01 00:00:00.000
7              34             2010-01-01 00:00:00.000
8              50             2010-01-01 00:00:00.000

Even when grouping by StoreID, the output of SELECT MAX(EndDate) from WorkSchedule would be 1/1/2010. Store 50 would restore an EndDate of January 1, 2010. According to the applicable business standards, we should pull back each store's maximum end date.

However, if the EndDate field is NULL, the store is still represented as active, thus we must replace all other mentioned dates with NULL. Fortunately, we can modify the query to persuade MAX to select the NULL value. We can move forward after adding a case statement to that.

We'll use COALESCE to replace any NULL EndDate with a future date that won't appear anywhere in our data; December 31, 2099, seems like a good choice for this. The MAX of the dates, which if NULL evaluates to 12/31/2099 and is greater than any other date in our table, is the next step. To replace the date 12/31/2099 back to NULL and group our data by StoreID, wrap that in a CASE statement.

SELECT StoreID,
CASE WHEN MAX(COALESCE(EndDate, ’12/31/2099')) = ’12/31/2099' THEN NULL ELSE MAX(EndDate) END AS Date
FROM WorkSchedule
GROUP BY StoreID

It's not the most elegant way to do things, but it definitely gets the job done. This works even at the minimum. If you want to minimize nulls, select a very low date, such as 1/1/1900. The same method can be used for regular integer fields by replacing NULL with a number.

If you use AVG and other aggregate functions with null values, this is a great way to do that. This can be achieved by using the SELECT AVG (COALESCE (column, 0)) FROMTable, which may average null values ​​as zero. Then any NULL is treated as NULL.

Example: I found myself trying to get Postgres to show data from the larger of two columns as part of a database view.

My first attempt was:

SELECT id, MAX(column1, column2) FROM table1;

which of course didn't work because MAX() is an aggregate function which only takes a single parameter.

What I was looking for instead, is something that was introduced in 8.1:

SELECT id, GREATEST(column1, column2) FROM table1;

where GREATEST() (and its opposite: LEAST()) is a conditional expression.

You can also use the SQL MAX() aggregate function in the WHERE and HAVING clauses of a straightforward SELECT query.

Example 1: As demonstrated in the SQL SELECT statement below, this can be done by comparing a particular column value with the outcome received via a subquery that produced the maximum value of that particular column:

Sample table : customer

To get data of 'cust_country' and maximum 'outstanding_amt' from the 'customer' table with following conditions:

'cust_country' should be formatted in a group, 'grade' must be 2, the following SQL statement can be used :

SELECT cust_country,MAX(outstanding_amt) 
FROM customer 
WHERE grade=2 
GROUP BY cust_country;

Output :

CUST_COUNTRY         MAX(OUTSTANDING_AMT)
-------------------- --------------------
USA                                  6000
India                               12000
Australia                            5000
Canada                               8000
UK                                   6000

Example 2: Let’s examine the payment table in the sample database.

The following query uses the MAX() function to find the highest amount paid by customers in the payment table:

SELECT MAX(amount)
FROM payment

PostgreSQL MAX function in subquery:

To get other information together with the highest payment, you use a subquery as follows:

SELECT * FROM payment
WHERE amount = (
   SELECT MAX (amount)
   FROM payment
);

The outer query chooses all rows whose amounts are equal to the highest payment obtained from the subquery after the subquery utilizes the MAX() function to return the highest payment.

To determine the minimum or lowest value of a column over each group against a condition, utilise the SQL HAVING clause and SQL MIN() function. Column NULL values are ignored.

In SQL Server, this function falls under the category of aggregation functions. In their result, aggregate functions execute calculations on a number of values from an expression and produce a single value. Unless your source data is altered, aggregate functions always return the same value.

Syntax:

The syntax of this SQL function is given below.

SELECT MIN (column)
FROM table
GROUP BY
HAVING

Example to prove the syntax :

I have two tables: "Sales" and "Employee." The "Sales" table contains information about sales and invoices, whereas the "Employee" table has all the information on employees. For this tutorial, I'll utilise these two tables in the use cases following. You can generate these use cases with a few sets of values if you don't already have a table to test them on. The queries in this article will produce results based on the information kept in these tables.

Example 1: The following query finds uses the MIN() function to find the lowest replacement costs of films grouped by category and selects only groups that have replacement cost greater than 9.99.

SELECT name category,
	MIN(replacement_cost) replacement_cost
FROM category
INNER JOIN film_category USING (category_id)
INNER JOIN film USING (film_id)
GROUP BY name
HAVING MIN(replacement_cost) > 9.99
ORDER BY name;

Example 2: On this page, we'll go through how to utilise the SQL HAVING clause and MIN() function to compare each group's lowest value of a column to a set of conditions.

Sample table: customer

To get data of 'cust_city', 'cust_country' and minimum or lowest values of 'outstanding_amt' from the 'customer' table with following conditions:

The combination of cust_country and cust_city should make a group, 'outstanding_amt' must be more than 6000, the following SQL statement can be used :

SELECT cust_city, cust_country, 
MIN (outstanding_amt) 
FROM customer 
GROUP BY cust_country, cust_city 
HAVING MIN (outstanding_amt)>6000;

Output:

CUST_CITY        CUST_COUNTRY    MIN(OUTSTANDING_AMT)
Bangalore            India                      8000
Chennai              India                      8000
Mumbai               India                      9000

Example 3: Let's look at another component of the aforementioned query or example; this will be an expansion of the aforementioned query that includes the HAVING clause by adding a line of code. If your minimum invoice cost was $150 or more, you might wish to track down all of those months. In other words, we may state that every invoice processed in that month was priced at or over $150, and not a single invoice was.

The following search will bring up all months where even the minimum invoice is still higher than $150.

SELECT InvoiceMonth, MIN (price) AS [Lowest Invoice]
FROM Sales
GROUP BY InvoiceMonth
HAVING MIN(price)>150
GO

All invoices over $150 were processed in July, while the lowest invoice that was processed in that month was for $299.

Example 4: Use SQL MIN function with HAVING statement:

Let's look at another component of the aforementioned query or example; this will be an expansion of the aforementioned query that includes the HAVING clause by adding a line of code. If your minimum invoice cost was $150 or more, you might wish to track down all of those months. In other words, we may state that every invoice processed in that month was priced at or over $150, and not a single invoice was.

The following search will bring up all months where even the minimum invoice is still higher than $150.

SELECT InvoiceMonth, MIN (price) AS [Lowest Invoice]
FROM Sales
GROUP BY InvoiceMonth
HAVING MIN(price)>150
GO

July is the month in which we have processed all invoices greater than $150 and the lowest invoice processed in that month was at $299.

Example 5: If you want a record of all the months in which your minimum invoice was processed that was less than $150 and not greater than $150, let's invert this requirement. In other words, the following query will include all months where the lowest invoice price is less than $150 but will only return months where we have invoiced at least one sale for less than $150.

SELECT InvoiceMonth, MIN (price) AS [Lowest Invoice]
    FROM Sales
    GROUP BY InvoiceMonth
    HAVING MIN(price)<150
    GO

The results of the aforementioned query are displayed in the image below, and as can be seen, there are no details for the month of July.

Example 1:

Consider below simple table:

Name     Salary
---------------
abc     100000
bcd     1000000
efg     40000
ghi     500000

How to identify the worker with the second-highest pay. For instance, "ghi" has the second-highest salary in the table above at $500,000.

The basic search query to locate the employee making the highest income is below.

select *from employee where salary=(select Max(salary) from employee);

We can nest the above query to find the second largest salary.

Way 1:

select *from employee 
group by salary 
order by  salary desc limit 1,1;

Way 2:

SELECT name, MAX(salary) AS salary 
FROM employee 
WHERE salary IN
(SELECT salary FROM employee MINUS SELECT MAX(salary) 
FROM employee); 

Way 3:

SELECT name, MAX(salary) AS salary 
FROM employee 
WHERE salary <> (SELECT MAX(salary) 
FROM employee);

Way 4:

IN SQL Server using Common Table Expression or CTE, we can find the second highest salary:

WITH T AS
(
SELECT *
   DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rnk
FROM Employees
)
SELECT Name
FROM T
WHERE Rnk=2;

Example 2: In this tutorial, I am going to explain to you how to find the second highest salary in multiple ways.

First, we will create a Database and a table.

Creating a Database and One Table:

Step 1 - Create a Database

Open your SQL Server and use the following script to create the “chittadb” Database.

Create database chittadb  

To run the aforementioned script, first choose the query and then hit F5 or click the Execute button.

A notice stating "Command(s) completed successfully" ought to appear. Your new database has been created, thus this means.

Step 2 - Create a table

Open your SQL Server and use the following script to create a table “tbl_Employees”.

Create table tbl_Employees  
(  
     Id int primary key not null identity(1,1),  
     FirstName varchar(50),  
     LastName varchar(20),  
     Location varchar(20),  
     Gender varchar(50),  
     Salary int  
)  

Execute the above query to create “tbl_Employees “.

You should see a message, “Command(s) completed successfully.”

Step 3: Insert Data

Now, the data has been inserted into the table.

Insert into tbl_Employees values ('Chittaranjan', 'Swain','Odisha', 'Male', 80000)  
Insert into tbl_Employees values ('Chandin', 'Swain', 'Pune','Female', 76000)  
Insert into tbl_Employees values ('Mitu', 'Pradhan','Delhi', 'Male', 55000)  
Insert into tbl_Employees values ('Jeni', 'Swain','Chennai', 'Female', 76000)  
Insert into tbl_Employees values ('Adyashree', 'Swain','UK', 'Female', 49000)  
Insert into tbl_Employees values ('Ram', 'Kumar','US', 'Male', 39000)  
Insert into tbl_Employees values ('Jitendra', 'Gouad','Hydrabad', 'Male', 35000)  
Insert into tbl_Employees values ('Dibas', 'Hembram','Bangalore', 'Male', 55000)  

Execute the above query, you should see a message, “Command(s) completed successfully.

Step 4: Display Records

Now retrieve all data from the “tbl_Employees” table.

select * from tbl_Employees  

Step 5: How To Find Second Highest Salary:

select distinct top 2 salary from tbl_Employees order by Salary desc 

Example 3: We must identify the employee with the second-highest salary from a table of employees with employee details. Either the employee's details must be printed, or we can merely print the employee's salary.

Let us Consider a Table Employees with the following attributes and records:

ID	NAME	SALARY
1	Amanda Jones	12000
2	Steve Madden	15000
3	Robert Henry	10000
4	Shawn Adams	10000
5	Luke Shaw	11000

Each employee's Attributes ID, NAME, and SALARY are listed in the Employees table. With a salary of $12,000, Amanda Jones, an employee in the relationship, has the 2nd highest salary.

One SELECT can employ both the MIN and MAX methods. A GROUP BY clause is not necessary if you solely employ these functions, without using any columns.

All non-aggregated columns should be listed in the GROUP BY clause when using the SQL MAX() aggregate function to return the maximum value of a single column with other column values.

All non-aggregated columns should be mentioned in the GROUP BY clause when using the SQL MIN() aggregate function to return the minimum value of a certain column with additional column values.

Example 1: Below we have a query that implements both functions:

SELECT MIN(price) AS min_price,
  MAX(price) AS max_price
FROM cosmetics;

Result:

Example 2: The MIN() function with the price column input comes first in the SELECT statement, followed by MAX() with the same argument and their respective synonyms. This gives the lowest ($3) and highest ($22) prices for each item in the table.

Naturally, you may achieve the same results by figuring out the lowest and highest prices for each category. However, in this case, GROUP BY is required.

SELECT category,
  MIN(price) AS min_price,
  MAX(price) AS max_price
FROM cosmetics
GROUP BY category;

Result:

category	min_price	max_price
hair	           3	            21
face	           5	            21
eye	           8	             22

In this instance, the lowest price in the "hair" category is $3, while the highest price in the "eye" category is $22, and vice versa.

Example 3: What are each country's minimum and maximum freight weights? We require the GROUP BY clause in order to respond to this query. The illustration is at Listing 2 down below.

The query gives the minimum and maximum freight weights, respectively, for each country after first grouping the data by shipcountry. By running the last search in the listing, we can verify this (validation).

MIN and MAX Freight By Country

Minimum by Country

SELECT shipcountry, MIN(freight) min_freight FROM [Sales].[Orders]
GROUP BY shipcountry;

Maximum by Country

SELECT shipcountry, MAX(freight) max_freight FROM [Sales].[Orders]
GROUP BY shipcountry;

Validation

SELECT * FROM [Sales].[Orders] WHERE shipcountry='Finland'
ORDER BY freight;

Listing 2: MIN and MAX Freight by Country

Minimum by Customer

SELECT custid, MIN(freight) min_freight FROM [Sales].[Orders]
GROUP BY custid;

Maximum by Customer

SELECT custid, MAX(freight) max_freight FROM [Sales].[Orders]
GROUP BY custid;

Validation

SELECT * FROM [Sales].[Orders] WHERE custid='23'
ORDER BY freight;

Example 4: To obtain the maximum value for each group, utilise the MAX function with the GROUP BY clause. For instance, the following query returns the amount that each customer has paid in full.

SELECT customer_id,
	MAX (amount)
FROM
	payment
GROUP BY
	customer_id;

Example 5: To group the output depending on the supplied expression, this function is frequently used with the GROUP BY statement. The GROUP BY statement is useful in a variety of usage cases. Please allow me to demonstrate one requirement where you are expected to list the lowest invoice from each month. By using this function and the GROUP BY statement on the month's column, you may complete it quickly.

SELECT InvoiceMonth, MIN (price) AS [Lowest Invoice]
FROM Sales
GROUP BY InvoiceMonth
GO

This example will group all invoices on monthly basis and return the lowest invoice price from each month as shown.

Example 6: The SELECT statement below will list the birthdates and salaries of all employees and return the maximum value next to each value so that you may compare each number to the maximum value, as shown below:

SELECT TOP 10 ID, EmpName, EmpDateOfBirth, 
   YoungEmp = (SELECT MAX(EmpDateOfBirth) FROM MAXDemo), EmpSalary, 
   LargeSalary = (SELECT MAX(EmpSalary) FROM MAXDemo )
FROM MAXDemo
WHERE EmpIsActive = 1
GROUP BY ID, EmpName, EmpDateOfBirth , EmpSalary
ORDER BY EmpSalary DESC

Example 7: The MIN() function and the GROUP BY clause are used in the following sentence to determine which films have the lowest replacement costs by category:

SELECT name category,
MIN(replacement_cost) replacement_cost
FROM category
INNER JOIN film_category USING (category_id)
INNER JOIN film USING (film_id)
GROUP BY name
ORDER BY name;

Example 8: Here is a more instance utilising MAX (). Assume that I now want to know the OwnershipPercentage per Customer of the most recent product introduced.

Deploying GROUP BY and the aggregate Max() function in a sub-query is one remedy for this. A SELECT statement contained within another SQL statement is a sub-query.

select c.Name, 
c.ownershippercentage,
c.Effective_date as effective_date
from Customer c
inner join (
select Name, 
max(Effective_date) as max_date
from Customer group by Name) d
on c.Name = d.Name
and c.Effective_date = d.max_date

The sub-query will yield the table I referred to as "d." I can retrieve the most recent Effective Date for each Customer in Table "d" using this sub-query. To determine the OwnershipPercentage for that most recent effective date, I use a JOIN operation between Table "Customer" and Table "d" to gather this information. Although the aforementioned query will give you the necessary results, it is not the best choice. The complexity increased because we had to employ a JOIN statement and combine aggregate MAX() and GROUP BY in a sub-query. The following code is more effective:

>SELECT c.ID, c.Name, c.ProductID, c.OwnershipPercentage, c.Effective_Date
FROM Customer c
WHERE c.Effective_Date = (SELECT MAX(p.Effective_Date) FROM Customer p WHERE p.ID = C.ID)

When filtering rows using the value returned by this function, i.e. in the HAVING clause, use MIN() or MAX().

The query below implements MIN() in HAVING:

SELECT category,
  MAX(price) AS max_price
FROM cosmetics
GROUP BY category
HAVING MIN(price)>4;

Result:

We have the column category in the SELECT. The MAX() function is the next, with price as the input. We will determine the highest price among products in each category. The GROUP BY clause with the column category follows the FROM cosmetics clause.

The HAVING clause with the MIN() function appears at the conclusion of the statement. In each category, the lowest price will be found; if it is less than 4, it will not be included in the findings. The "hair" category's $3 minimum price prevented it from appearing in the result set.

You should be aware that utilising either function in HAVING is not contingent upon using it (or any other aggregate function) in SELECT.