Mysql : query in a query (subquery) not working

Question

I want to run a MySQL query on a table then a subquery on this table. I have a list of objects. Each object has a major and a minor version. For one object I'm trying to find which is the "LAST VERSION" of this object : this mean that I want to find the max(major) for this object and then the max(minor) on the last result.

I build a "test" MySQL database :

create database test ;  
use test ;

I created a table :

create table test 
(  
    id int auto_increment,   
    object varchar(10),   
    major int,   
    minor int,  
    PRIMARY KEY (`id`)  
) engine innodb;

I fill this table with data :

insert into test (object, major, minor) values ('obj1',1,0) ;  
insert into test (object, major, minor) values ('obj1',1,1) ;  
insert into test (object, major, minor) values ('obj1',1,2) ;  
insert into test (object, major, minor) values ('obj1',2,0) ;  
insert into test (object, major, minor) values ('obj1',2,1) ;

I list the table : select * from test;

+----+--------+-------+-------+  
| id | object | major | minor |  
+----+--------+-------+-------+  
|  1 | obj1   |     1 |     0 |  
|  2 | obj1   |     1 |     1 |  
|  3 | obj1   |     1 |     2 |  
|  4 | obj1   |     2 |     0 |  
|  5 | obj1   |     2 |     1 |  
+----+--------+-------+-------+

5 rows in set (0,01 sec)

The first query is to get the max(major) lines :

select * 
from test 
where object = 'obj1' 
  and major = (select max(major) from test);

with this result :

+----+--------+-------+-------+  
| id | object | major | minor |  
+----+--------+-------+-------+  
|  4 | obj1   |     2 |     0 |  
|  5 | obj1   |     2 |     1 |  
+----+--------+-------+-------+

2 rows in set (0,00 sec)

Then I try to get the minor 0 version :

select * 
from 
    (select * 
     from test 
     where object = 'obj1' 
       and major = (select max(major) from test)) as t 
where 
    t.minor = 0 ;

and it works, the result is :

+----+--------+-------+-------+  
| id | object | major | minor |  
+----+--------+-------+-------+  
|  4 | obj1   |     2 |     0 |  
+----+--------+-------+-------+

but I want the LAST VERSION, so I want to find the max(minor) which is 1 :

select * 
from 
    (select * 
     from test 
     where object = 'obj1' 
       and major = (select max(major) from test)) as t 
where 
    t.minor = (select max(minor) from t) ;

And I get the error :

ERROR 1146 (42S02): Table 'test.t' doesn't exist

I don't understand why it doesn't work.

Thanks

Forgot to say "Hi" but edit doesnt work !

Fabrice
– Fabrice

2019-06-13 09:50:16 +00:00
Commented Jun 13, 2019 at 9:50 — Fabrice
– Fabrice, Commented Jun 13, 2019 at 9:50
What is expectation from last query?

Raka
– Raka

2019-06-13 10:11:43 +00:00
Commented Jun 13, 2019 at 10:11 — Raka
– Raka, Commented Jun 13, 2019 at 10:11

Booboo · Accepted Answer · 2019-06-14 11:03:59Z

2

select * from test where object = 'obj1' order by major desc, minor desc limit 1;

First we sort the table so that all the rows with the largest values of column major come first (order by major desc). If there are any rows with the same value for column major, we then "break ties" by ordering the rows with like values for column major by ordering them so that rows with the largest values of column minor come first (order by minor desc). Thus the first row output would be the row with the largest value of major and for that value of major the largest value of minor. Since we are only interested in that first row, we limit the output to one row (limit 1).

edited Jun 14, 2019 at 11:03

answered Jun 13, 2019 at 13:41

Booboo

45.7k4 gold badges46 silver badges74 bronze badges

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

1 Comment

Booboo Over a year ago

@Nic3500: Good point. I have tried to explain why the SQL solves the problem. Thanks.

Raka · Accepted Answer · 2019-06-13 10:13:46Z

0

you can achieve the same with below query

select * from test where object='obj1' and major=(select max(major) from test)
and minor = (
select max(minor) from test where object='obj1' and major=(select max(major) from test )
  )

this will give below result

+----+--------+-------+-------+
| id | object | major | minor |
+----+--------+-------+-------+
| 5 | obj1 | 2 | 1 |
+----+--------+-------+-------+

edited Jun 13, 2019 at 10:13

answered Jun 13, 2019 at 10:05

Raka

4272 silver badges10 bronze badges

1 Comment

Fabrice Over a year ago

I didn't think about the "select max(minor)", thanks

Collectives™ on Stack Overflow

Mysql : query in a query (subquery) not working

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related