mysql query if condition

Question

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

eggyal · Accepted Answer · 2012-10-12 16:27:28Z

1

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.

answered Oct 12, 2012 at 16:27

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

troy Over a year ago

Ola...Thank You Sir,It worked.... will Natural Join have any performance effenciency over left Join..Just out of curiosity

eggyal Over a year ago

@troy: Any performance difference is insignificant. The key differences are that NATURAL JOIN is an inner join whereas LEFT JOIN is an outer join (indeed, they are actually shorthands for NATURAL INNER JOIN and LEFT OUTER JOIN respectively); also, NATURAL JOIN performs the join on all identically-named columns in both tables, whereas LEFT JOIN requires one to explicitly name the columns/criteria on which the join should be performed.

Adam Plocher · Accepted Answer · 2012-10-12 16:27:11Z

0

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.

answered Oct 12, 2012 at 16:27

Adam Plocher

14.3k6 gold badges51 silver badges83 bronze badges

1 Comment

eggyal Over a year ago

In MySQL it's syntactically valid, but produces indeterminate results (the server is free to choose any value from each group for "hidden" columns serverTime and stage).

user1741851 · Accepted Answer · 2012-10-12 17:47:43Z

0

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.

answered Oct 12, 2012 at 17:47

user1741851

Collectives™ on Stack Overflow

mysql query if condition

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related