CODEWITHSUNDEEP
mysqlsql
Amrith Krishna
Amrith Krishna

Reputation: 2853

Using max in where clause

for a particular query where I need to show an entire tuple, based on the max value of a particular field, for this I need to use max at where clause, how can I use it? Right now I am achieving the same by limit 1

select * from  
  (select count(some_field) as field1 ..........order by field1 desc) as kil 
limit 1

Upvotes: 0

Views: 95

Answers (2)

SoulTrain
SoulTrain

Reputation: 1904

I am not well-versed in mysql but in SQL server You could do it like this..

SELECT 
  * from 
table A
where col1=(select max(col1) from table B where A.id=B.id)

for Count, you could do

SELECT 
    col1
    , col2  from 
    table A
group by 
col1
, col2
Having count(*)>1

Upvotes: 0

Bohemian
Bohemian

Reputation: 424953

Try this:

select * from some_table
where field1 = (select max(field1) from some_table)

Note that your original query will return one row, but this query may return multiple rows if there are multiple rows that share the same maximum value. To duplicate the same behaviour, you would still have to add limit 1 to the end of this query.

Upvotes: 1

Related Questions