CODEWITHSUNDEEP
mysqlsql
jaggedsoft
jaggedsoft

Reputation: 4038

What's the proper way to combine these two queries into one?

select gmt from history.cachemap order by gmt asc limit 0,1;
..this returns gmt: 2012-05-03
select gmt from history.cachemap order by gmt desc limit 0,1;
..this returns gmt: 2012-09-15

A little background on the database I'm working with, I'm trying to get the earliest and latest date fields from a list of several million records.

Earlier this week I posted this question:

How to combine three MySQL queries into one?

This question was answered perfectly, but doesn't apply to this type of query.

Upvotes: 0

Views: 72

Answers (7)

Tim Lamballais
Tim Lamballais

Reputation: 1054

This can be done in a single query as follows:

SELECT MIN(gmt), MAX(gmt) FROM history.cachemap

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271161

I think the best way is to use the max and min functions. You can get the results as one row:

select min(gmt), max(gmt)
from history.cachemap

This is the most efficient solution, since it involves only one pass through the data.

Alternatively, you can put this into two rows, using union all:

select 'min' as which, min(gmt)
from history.cachemap union all
select 'max', max(gmt)
from history.cachemap

UNION/UNION ALL does not return rows in a specified order, so you should include information on which is which (or order them explicitly).

Upvotes: 1

Piotr Wadas
Piotr Wadas

Reputation: 1894

if history.cachemap contains millions of records (rows), it would be probably better with

SELECT gmt FROM history.cachemap WHERE gmt = max(gmt) OR gmt = min(gmt) ...

Something like that.. note, that with UNION you actually do the same query twice, to the same table, which contains million of records..

Upvotes: 0

LSerni
LSerni

Reputation: 57453

The more efficient way, but returning two columns instead of one, would be:

select MIN(gmt) AS mingmt, MAX(gmt) AS maxgmt
   FROM history.cachemap;

With indexing on gmt, in MySQL, it should be pretty much instantaneous.

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125544

select 
    (select min(gmt) from history.cachemap),
    (select max(gmt) from history.cachemap)

Upvotes: 1

NedStarkOfWinterfell
NedStarkOfWinterfell

Reputation: 5243

You can do this:

SELECT GMT FROM HISTORY.CACEMAP ORDER BY GMT DESC LIMIT 0,1 UNION SELECT GMT FROM HISTORY.CACEMAP ORDER BY GMT ASC LIMIT 0,1;

Upvotes: 0

Jocelyn
Jocelyn

Reputation: 11413

(select gmt from history.cachemap order by gmt asc limit 0,1)
UNION
(select gmt from history.cachemap order by gmt desc limit 0,1)

Documentation: UNION syntax

Upvotes: 1

Related Questions