CODEWITHSUNDEEP
mysqlmaxmin
Ash501
Ash501

Reputation: 331

Get more info from a MAX(ID), MIN(ID) MYSQL query?

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

Need to get something like this-pseudo-query:

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' that equals 'white'.

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

But how?

Upvotes: 7

Views: 14813

Answers (3)

Mark Byers
Mark Byers

Reputation: 839254

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 variation which 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
)

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

Upvotes: 6

Zane Bien
Zane Bien

Reputation: 23135

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)

Upvotes: 4

martin
martin

Reputation: 2648

It's exactly 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 select in parentheses is the inner select that you can use just like another table.

Upvotes: 0

Related Questions