- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- 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
Write a DB2 query to find out all the duplicate INVOICE_ID in ORDERS DB2 table?
The duplicate INVOICE_ID in ORDERS DB2 table can be found by using a combination of GROUP BY with HAVING clause and COUNT function. The GROUP BY will enable us to group the result invoice wise and COUNT function will enable us to count the number of occurrences in each group. We can use predicate in HAVING clause to filter the results for groups having count greater than one.
Below is the SQL query which we can use to find duplicate invoices in ORDERS DB2 table.
SELECT INVOICE_ID FROM ORDERS GROUP BY INVOICE_ID HAVING COUNT(INVOICE_ID) > 1
For example, if we have ORDERS DB2 table as below.
The query ”SELECT INVOICE_ID FROM ORDERS GROUP BY INVOICE_ID
HAVING COUNT(INVOICE_ID) > 1” will return the result below.
- Related Articles
- Write the DB2 SQL query to find the third highest ORDER_TOTAL in a ORDERS DB2 table
- Write a SQL query to count the number of duplicate TRANSACTION_ID in an ORDERS DB2 table
- Write the syntax to declare a scrollable cursor on the ORDERS DB2 table.
- How to find out all the indexes for a DB2 table TAB1?
- How will you find out all the indexes which are built in a particular DB2 table?
- How to update DB2 table with a duplicate primary key?
- How to get the list of all COBOL-DB2 programs using a DB2 table TAB1?
- How can you revert all the DB2 table changes done in a COBOL-DB2 program?
- How to find all the foreign keys of a DB2 table TAB1?
- How will you find the ORDER_ID of all the orders having ORDER_TOTAL greater than the average of ORDER_TOTAL in ORDER's DB2 table
- How will you create a new TRIGGER on the ORDERS DB2 table? Give the syntax of TRIGGER
- How to create a DB2 table TAB1 with 4 columns, Student ID, Enrollment ID, Name and Age?
- How to find the primary key of a DB2 table TAB1?
- How to delete a DB2 table TAB1?
- How to store a NULL value in a particular column of a DB2 table using COBOL-DB2 program?