Get more information from the query MAX (ID), MIN (ID) MYSQL?

Question

How to get more columns from MAX(ID), MIN(ID) MYSQL query?

Currently, I get only two values: MAX(ID) & MIN(ID) from this query:

SELECT MIN(ID), MAX(ID) FROM mytable WHERE mytable.series = 'white' ;

You need to get something like this-pseudo-request:

 SELECT column1, column2 FROM mytable WHERE series = 'white' AND ID=Max(ID) 'AND GET ME ALSO' WHERE series = 'white' AND ID=Min(ID);` 

It should return 2 rows for the column 'series', which is equal to 'white'.

1 with column1 and column2 for ID = Min (ID). 2 with column1 and column2 for ID = Max (ID).

But how?

Answer 1

Here is an approach using UNION :

 SELECT column1, column2 FROM mytable WHERE series = 'white' AND ID IN ( SELECT MIN(ID) FROM mytable WHERE series = 'white' UNION SELECT MAX(ID) FROM mytable WHERE series = 'white' ) 

For good performance, add a combined index on (series, id) .

Or another option that may have better performance:

 ( SELECT column1, column2 FROM mytable WHERE series = 'white' ORDER BY ID LIMIT 1 ) UNION ( SELECT column1, column2 FROM mytable WHERE series = 'white' ORDER BY ID DESC LIMIT 1 ) 

It will also be able to use the combined index on (series, id) .

Answer 2

A simpler solution:

 SELECT a.column1, a.column2 FROM mytable a JOIN ( SELECT MIN(ID) AS minid, MAX(ID) AS maxid FROM mytable WHERE series = 'white' ) b ON a.ID IN (b.minid, b.maxid) 

Answer 3

This is exactly the same as you say:

 SELECT column1, column2 FROM mytable as m, (SELECT MIN(ID) as mid, MAX(ID) as xid FROM mytable WHERE mytable.series = 'white' ) t WHERE m.ID = t.mid or m.ID = t.xid; 

The selection in parentheses is an internal selection that can be used in exactly the same way as in another table.