MySQL multiple subquery

Question

I want to query something like this:

SELECT id 
FROM reparacoes 
WHERE edit_data= (select max(edit_data) 
                  from reparacoes 
                  where id_reparacao= (select id_reparacao from reparacoes));

However the subquery select id_reparacao from reparacoes returns more than 1 row (as expected) and the query doesn't work. Should I do this with joins? if yes, how?

So here's how it works: every time I edit a "reparacoes" row(at my website), I INSERT a new one(i don't UPDATE) and a new ID is created as well as a edit_data(which is the datetime of the edit) but the id_reparacoes stays the same.

I want a query that returns the ID from reparacoes where edit_data is the most recent from a given id_reparacao. but I have more then one id_reparacao and i want this to query for all id_reparacao(that's why i tried ...where id_reparacao= (select id_reparacao from reparacoes)

PS: sorry, I know this has already been questioned/answered many times but I couldn't find anything that could help me.

What do you expect as result? Please give an example, it's unclear what you want from this query. — Math
– Math, Commented Jun 27, 2013 at 11:25
Your last query is useless anyway. What are you trying to do ? Explain a bit your database schema (only the relevant part), I think the answer is way simpler than anything you think — Laurent S.
– Laurent S., Commented Jun 27, 2013 at 11:25

GarethD · Accepted Answer · 2013-06-27 11:41:31Z

3

I think you want something like this:

SELECT  rep.ID
FROM    reparacoes rep
        INNER JOIN
        (   SELECT  id_reparacao, MAX(Edit_Data) AS Edit_Data
            FROM    reparacoes
            GROUP  BY id_reparacao
        ) MaxRep
            ON MaxRep.id_reparacao = rep.id_reparacao
            AND MaxRep.Edit_Data = rep.Edit_Data;

The subquery simply gets the last edit_data per id_reparacao, then by doing and INNER JOIN back to the table you limit the rows to only those where the Edit_data matches the latest per id_reparacao

answered Jun 27, 2013 at 11:41

GarethD

70k11 gold badges90 silver badges130 bronze badges

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

1 Comment

Correia JPV Over a year ago

Working perfectly! Exactly as i wanted! Sorry if i didnt expressed myself properly in the question, anyway thank you

Math · Accepted Answer · 2013-06-27 11:42:58Z

0

I guess what you want is this:

SELECT id
 FROM reparacoes
 WHERE edit_data = (
   SELECT max(edit_data)
   FROM reparacoes
);

edited Jun 27, 2013 at 11:42

answered Jun 27, 2013 at 11:31

Math

3,3904 gold badges41 silver badges53 bronze badges

Comments

cmenke · Accepted Answer · 2013-06-27 11:46:28Z

0

I'm not sure I fully understand what you want the query to return. It might help if you described your goal in words.

If you want to get the ID with the maximum edit data per distinct id_reparacao, try this:

SELECT id 
FROM reparacoes outerquery
WHERE edit_data = (SELECT max(edit_data) 
                   FROM reparacoes innerquery
                   WHERE innerquery.id_reparacao = outerquery.id_reparacao)

edited Jun 27, 2013 at 11:46

answered Jun 27, 2013 at 11:30

cmenke

1641 silver badge9 bronze badges

Comments

Jorge Y. C. Rodriguez · Accepted Answer · 2014-10-16 15:43:41Z

0

Use in instead of = if the inner query returns more than one row.

SELECT id 
FROM reparacoes 
WHERE edit_data in 
        (select max(edit_data) 
         from reparacoes 
         where id_reparacao in
                 (select id_reparacao from reparacoes)
        );

edited Oct 16, 2014 at 15:43

Jorge Y. C. Rodriguez

3,4665 gold badges42 silver badges64 bronze badges

answered Jun 27, 2013 at 11:27

shashankg77

2,3464 gold badges20 silver badges36 bronze badges

3 Comments

Math Over a year ago

the last select is totally unseless, as said before by Bartude

shashankg77 Over a year ago

I posted it because the question seems to be coming from a person just playing around with sql and learning the basics. I know the last select is irrelevant :)

Correia JPV Over a year ago

true, I'm now very experienced with sql, and can't do subquerys all by myself yet

Shiv · Accepted Answer · 2013-06-27 11:46:58Z

-1

Use below query if you want to compare single column data:

SELECT id FROM reparacoes WHERE edit_data= (select max(edit_data) from reparacoes);

Use below query if you want to compare from multiple records:

SELECT id FROM reparacoes WHERE edit_data IN (select edit_data from reparacoes);

edited Jun 27, 2013 at 11:46

answered Jun 27, 2013 at 11:36

Shiv

693 bronze badges

2 Comments

Correia JPV Over a year ago

Just edited and added detailed info. I don't know if it will help though

GarethD Over a year ago

This should be a comment not an answer

Collectives™ on Stack Overflow

MySQL multiple subquery

5 Answers 5

1 Comment

Comments

Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related