CODEWITHSUNDEEP
mysqlsql
Sagar
Sagar

Reputation: 1319

Select all rows where specific column value is maximum of that column

I want to select all columns(col1, col2, col3) where value of col3 is the maximum amongst all the values in col3.

For ex, for below table

col1 col2 col3
abc  def  2
ghi  jkl  3
mno  pqr  2
stu  vwx  3
yza  bcd  1

I want the output as

ghi  jkl  3
stu  vwx  3

How can I achieve this using SQL?

Upvotes: 0

Views: 43

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

Try this-

SELECT * 
FROM your_table 
WHERE col3 = (SELECT MAX(col3) FROM your_table)

Upvotes: 0

stud3nt
stud3nt

Reputation: 2143

There are multiple ways to achieve this. One way is to use a sub-query to find the maximum value of col3 and then use it in getting the desired result.

SELECT * 
   FROM TABLE
WHERE col3 = (SELECT MAX(col3) FROM TABLE)

Upvotes: 1

Related Questions