Using max in where clause

Question

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

What's the main problem with the query you have? Performance? Incorrect result? — Joachim Isaksson, Mar 20 '14 at 14:15
Is there any other better method to achieve the same, I mean to boost performance? @JoachimIsaksson — Amrith Krishna, Mar 20 '14 at 14:20
http://stackoverflow.com/questions/2905292/where-vs-having Believe you want Having. — EarlOfEnnui, Mar 20 '14 at 14:21
Do you have a `group by` in your inner query? You may need to show a bit more detail on your inner query to give a chance to optimize it. — Joachim Isaksson, Mar 20 '14 at 14:27
Perhaps, you should show sample data, and which row(s) you want back and explain why. — AgRizzo, Mar 20 '14 at 14:28

score 1 · Answer 1 · answered Mar 20 '14 at 14:16

1

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.

answered Mar 20 '14 at 14:16

Bohemian

412,405
93
575
722

the inner query needs to use somethingg like `select count(some_field) as field1` @Bohemian – Amrith Krishna Mar 20 '14 at 14:16
@AmrithKrishna why? what are you trying to do? Or do you want the max row for each value in another column? – Bohemian Mar 20 '14 at 14:18

score 0 · Answer 2 · answered Mar 20 '14 at 14:18

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

Using max in where clause

2 Answers2