CODEWITHSUNDEEP
mysql
troy
troy

Reputation: 1027

mysql query if condition

Hi there i have two tables a2_deal(I havent mentioned entire table as its very big)

deviceID    companyID   stage       serverTime
1             14          -1         1349449200 
1              1          -1         1349445600 
2             21          -1         1349449200 
3             17          -1         1349447160 
1             14           3         1344449200
1             14           2         1340449200

and another table called a2_comp

companyID   name
1           Microsoft
14          DELL
15          APPLE
17          Google

I am trying to get the most recent stage of a company By using below query:

SELECT deal.companyID, companies.name as Company,
if(max(serverTime),stage,Null) as Stage
FROM `a2_deal` AS deal
LEFT JOIN `a2_comp` AS companies ON deal.companyID = companies.companyID 
GROUP BY companyID
ORDER BY serverTime

in my query i am using if(max(serverTime),stage,Null) as Stage which means select the stage value related to most recent server time . ie it should give me -1 as the stage of companyID 14.... But for some reason i am not getting correct output..Please explain how my logic is wrong here... Thank You

Upvotes: 1

Views: 180

Answers (3)

user1741851
user1741851

Reputation:

Try this

SELECT deal.companyID, deal.stage, comp.name 
FROM a2_deal AS deal, a2_comp AS comp
WHERE deal.serverTime = 
     (SELECT MAX(deal2.serverTime) 
     FROM a2_deal AS deal2 
     WHERE deal2.companyID = deal.companyID)
AND comp.companyID = deal.companyID
GROUP BY deal.companyID

This might be a little confusing but the most interesting part is the sub query which selecting recent serverTime for each company. I have used theta style query and hence JOIN is not necessary.

Upvotes: 0

eggyal
eggyal

Reputation: 126025

You want the groupwise maximum:

SELECT a2_comp.*, a2_deal.*
FROM   a2_deal NATURAL JOIN (
  SELECT   companyID, MAX(serverTime) AS serverTime
  FROM     a2_deal
  GROUP BY companyID
) t JOIN a2_comp USING (companyID)

See it on sqlfiddle.

Upvotes: 1

Adam Plocher
Adam Plocher

Reputation: 14243

case is used for inline conditions in your query. Also, you may need to do

(case when max(serverTime) = serverTime then stage else null end) as Stage

I'm not totally sure that's valid, but you can try it out.

Upvotes: 0

Related Questions