CODEWITHSUNDEEP
mysqlsql
Andrea Covelli
Andrea Covelli

Reputation: 23

Mysql select if MAX val and other condition

I have the following table in MySql:

id|time|depth

id and time are primary keys. I need a query to select a row when the depth at the maximum time is not 0. Ideally I even need the row before maximum.

Let me do an example. The table could be something like this:

id       time      depth      
----------------------------
1        0          0   
1        10         1   
1        20         2    
2        0          0     
2        10         1

In this case it should return:

id       time      depth      
----------------------------  
1        20         2       
2        10         1

Thank you so much

Upvotes: 1

Views: 507

Answers (2)

Ranjana Ghimire
Ranjana Ghimire

Reputation: 1815

Try this: 

select id,max(time) from test1 where depth<>0 group by id

Upvotes: 0

e4c5
e4c5

Reputation: 53764

A join with a subquery is one way of doing it.

SELECT a.id, a.time, a.depth FROM Table1 a 
INNER JOIN
  (SELECT id, max(`time`) as mt FROM Table1 WHERE `depth` != 0 GROUP by id)  as b
ON a.id = b.id and a.time = b.mt

Upvotes: 1

Related Questions