![Trending Articles on Technical and Non Technical topics](/images/trending_categories.jpeg)
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is the SQL query describing usage of MAX aggregate function and GROUP-BY with HAVING?
We can find the highest ORDER_TOTAL datewise from the ORDERS DB2 table using below query.
Example
SELECT ORDER_DATE, MAX(ORDER_TOTAL) FROM ORDERS GROUP BY ORDER_DATE
We will use ‘GROUP BY’ on ORDER_DATE to group the result date wise and MAX aggregate function will help us to get the maximum ORDER_TOTAL placed at that particular date.
For example, if we have below ORDERS DB2 table.
ORDER_ID | ORDER_TOTAL | ORDER_DATE |
Z22345 | 342 | 29-07-2020 |
Z62998 | 543 | 30-07-2020 |
Z56990 | 431 | 28-07-2020 |
Z56902 | 6743 | 29-07-2020 |
Z99781 | 443 | 28-07-2020 |
Z56112 | 889 | 30-07-2020 |
Then the SQL query - SELECT ORDER_DATE, MAX(ORDER_TOTAL) FROM ORDERS GROUP BY ORDER_DATE will return the result below.
ORDER_DATE | ORDER_TOTAL |
28-07-2020 | 443 |
29-07-2020 | 6743 |
30-07-2020 | 889 |
Advertisements