CODEWITHSUNDEEP
mysqlsqlsubquery
Correia JPV
Correia JPV

Reputation: 610

MySQL multiple subquery

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.

Upvotes: 0

Views: 2481

Answers (5)

shashankg77
shashankg77

Reputation: 2306

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)
        );

Upvotes: 0

Shiv
Shiv

Reputation: 69

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);

Upvotes: -1

cmenke
cmenke

Reputation: 164

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)

Upvotes: 0

Math
Math

Reputation: 3396

I guess what you want is this:

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

Upvotes: 0

GarethD
GarethD

Reputation: 69759

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

Upvotes: 3

Related Questions