SQL SELECT with DISTINCT on multiple columns
DISTINCT on multiple columns
In SQL multiple fields may also be added with DISTINCT clause. DISTINCT will eliminate those rows where all the selected fields are identical.
Contents:
Example: Sample SELECT statement
Here is a simple query on some selected columns in orders table where agent_code='A002'
SQL Code:
SELECT agent_code, ord_amount, cust_code, ord_num
FROM orders
WHERE agent_code='A002';
Relational Algebra Expression:
Relational Algebra Tree:
Sample table: orders
Output:
AGENT_CODE ORD_AMOUNT CUST_CODE ORD_NUM ---------- ---------- ---------- ---------- A002 2500 C00005 200106 A002 500 C00022 200123 A002 500 C00009 200120 A002 500 C00022 200126 A002 3500 C00009 200128 A002 1200 C00009 200133 A002 4000 C00022 200113
The above result shows the same agent_code, ord_amount and cust_code appears more than once in theorders table.
Example: SELECT with DISTINCT on two columns
To get the identical rows (based on two columns agent_code and ord_amount) once from the orders table, the following SQL statement can be used :
SQL Code:
SELECT DISTINCT agent_code,ord_amount
FROM orders
WHERE agent_code='A002';
Relational Algebra Expression:
Relational Algebra Tree:
Output:
AGENT_CODE ORD_AMOUNT ---------- ---------- A002 3500 A002 1200 A002 4000 A002 500 A002 2500
Pictorial presentation:
Example: SELECT with DISTINCT on three columns
To get the identical rows (based on three columnsagent_code, ord_amount, and cust_code) once from the orders table, the following SQL statement can be used:
SQL Code:
SELECT DISTINCT agent_code, ord_amount,cust_code
FROM orders
WHERE agent_code='A002';
Relational Algebra Expression:
Relational Algebra Tree:
Output:
AGENT_CODE ORD_AMOUNT CUST_CODE ---------- ---------- ---------- A002 500 C00022 A002 3500 C00009 A002 2500 C00005 A002 500 C00009 A002 4000 C00022 A002 1200 C00009
Pictorial presentation:
Example : SELECT with DISTINCT on all columns of the first query
To get the identical rows (on four columns agent_code, ord_amount, cust_code, and ord_num) once from the orders table , the following SQL statement can be used :
SQL Code:
SELECT DISTINCT agent_code,ord_amount,cust_code,ord_num
FROM orders
WHERE agent_code='A002';
Relational Algebra Expression:
Relational Algebra Tree:
Output:
AGENT_CODE ORD_AMOUNT CUST_CODE ORD_NUM ---------- ---------- ---------- ---------- A002 500 C00022 200126 A002 2500 C00005 200106 A002 500 C00009 200120 A002 1200 C00009 200133 A002 4000 C00022 200113 A002 3500 C00009 200128 A002 500 C00022 200123
In the above output, all rows whose agent_code is 'A002' have returned because there is no identical rows on agent_code, ord_amount, cust_code and ord_num. See the following presentation :
Pictorial presentation:
SELECT with DISTINCT on multiple columns and ORDER BY clause
You can use an order by clause in the select statement with distinct on multiple columns. Here is an example:
SQL Code:
SELECT DISTINCT agent_code,ord_amount
FROM orders
WHERE agent_code='A002'
ORDER BY ord_amount;
Relational Algebra Expression:
Relational Algebra Tree:
Output:
AGENT_CODE ORD_AMOUNT ---------- ---------- A002 500 A002 1200 A002 2500 A002 3500 A002 4000
Pictorial presentation:
COUNT() function and SELECT with DISTINCT on multiple columns
You can use the count() function in a select statement with distinct on multiple columns to count the distinct rows. Here is an example:
SELECT COUNT(*)
FROM (
SELECT DISTINCT agent_code, ord_amount,cust_code
FROM orders
WHERE agent_code='A002');
Output:
COUNT(*) ---------- 6
Pictorial presentation:
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- Introduction
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework