SQL filtering for table based on 3rd column's value-CodePudding

How should my SQL be so that the table mentioned above should have the expected output using SQL. The filtering happens using the col 3 value. Row with the highest col 3 value for a particular col 1 value is selected in the output.

CodePudding user response：

You can use ROW_NUMBER() window function

SELECT col1, col2, col3 from (
    SELECT row_number() over (partition by col1 order by col3 desc) sn, * from your_table_name
) a WHERE sn=1;

CodePudding user response：

I assume you require the usage of the WHERE clause in SQL which acts as a method to filter results based on a conditional.

Considering you want all entries rather than any given entry one that matches, you can use the following (written in MySQL) which uses a nested SELECT statement:

SELECT *
FROM Table
WHERE col3=(SELECT MAX(col3) FROM Table);