Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL - Select the minimum value of a column after selecting the minimum value of another column

Tags:

sql

mysql

I have following Table (tablea):

       x            y
---------------------------
      3            1 
      1            4
      1            3

As result i want x = 1 and y = 3 (Getting x as low as possible is the top priority, getting y as low as possible is a lower priority). So I want an SQL Statement that is something like that:

Select Min(y), x 
FROM (SELECT MIN(x), y FROM tablea);

But this gives me following error code:

Error Code: 1248. Every derived table must have its own alias

If i try that:

Select Min(y), x 
FROM (SELECT MIN(x), y FROM tablea) as a;

I get:

Error Code: 1054. Unknown column 'x' in 'field list'

like image 753
Jariel Avatar asked Oct 11 '15 17:10

Jariel


1 Answers

To avoid error unknown column you need to add allias to MIN(x):

Select Min(a.y), a.x 
FROM (SELECT MIN(x) AS x, y FROM tablea) as a;

But to get results as you proposed I would rewrite your query as:

Select x, MIN(y) AS y
FROM tablea
WHERE x = (SELECT MIN(x) FROM tablea);

SqlFiddleDemo

like image 151
Lukasz Szozda Avatar answered Sep 29 '22 04:09

Lukasz Szozda