Comparing Two Rows in One Table Postgresql

 This article explain about SQL query which will compare two rows in one table PostgreSQL.
I wonder if the query can be implemented in another database, but it's just usual query I think, so it's possible to be implemented in another database such as MySQL, Oracle, SQL Server, and many more.

I assume you have had ability for installing Postgresql Database, creating database and table, and inserting the values.
Let us assume we have database named "comparing" (without quotes).

Create table named "table1" with the following columns.

Insert record as below:

We want to select single record of two duplicate value in "col1" column.
The single record which will be selected is the record which has the highest value in "col2" column for each duplicate "col1" column.
Below is query how to compare two rows in one table.

SELECT * FROM table1 t1 WHERE t1.col2 = (SELECT MAX(t2.col2) FROM table1 t2 WHERE t1.col1 = t2.col1 GROUP BY col1);

The result:

Query explanation:

SELECT * FROM table1 t1
Select all column with record in table1 {col1, col2, col3, col4) then we assign alias table name called "t1" (without quotes).

WHERE t1.col2=
Limit the record displayed based on rule on "col2' column (without quotes) which will be created afterward.

(SELECT MAX(t2.col2) FROM table1 t2 WHERE t1.col1 = t2.col1 GROUP BY col1);

This is the rules affected from "col2" column (without quotes).
We will select all columns in table1 which have maximum value in "col2" column (without quotes) from table1.
We assign alias table name called "t2" (without quotes).