MySQL min() and max()

dolan2go
dolan2go used Ask the Experts™
on
I have tried about 100 iterations of a query and not arrived at the solution.

The query should pick ONE row with: (in order) the MIN (runTime) and then MAX (Num)

One unsucessful attempt. 
SELECT Num, empNum, lastName, travelerName, runTime, fltNum, depCity, timeZone
FROM Check_in
WHERE runTime =
(SELECT MIN( runTime )
FROM Check_in
WHERE runTime BETWEEN '2011-05-16 09:19:00'
AND '2011-05-16 09:21:00')

Open in new window



Check-in.sql
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Kent OlsenDBA

Commented:
Hi Dolan,

Are you trying to pick two rows?  One for min and one for max?

Not sure how you're using all of the fields, but something like this should work:


Good Luck,
Kent
SELECT Num, empNum, lastName, travelerName, runTime, fltNum, depCity, timeZone
FROM Check_in
WHERE runTime in
(SELECT MIN (runTime ), MAX (runTime)
FROM Check_in
WHERE runTime BETWEEN '2011-05-16 09:19:00'
AND '2011-05-16 09:21:00')

Open in new window

dolan2go

Author

Commented:
@kdo,

Only 1 row.

1st pick MIN(runTime), then of those, pick the MAX(Num) and return that ONE row.
DBA
Commented:
Ok.  Try this one.

Kent

SELECT Num, empNum, lastName, travelerName, max(runTime), fltNum, depCity, timeZone
FROM Check_in
WHERE runTime =
(SELECT MIN( runTime )
FROM Check_in
WHERE runTime BETWEEN '2011-05-16 09:19:00'
AND '2011-05-16 09:21:00')

-- or

SELECT Num, empNum, lastName, travelerName, MaxrunTime, fltNum, depCity, timeZone
FROM Check_in, 
(
  SELECT MAX (runTime) MaxRunTime
  FROM Check_in
  WHERE runTime BETWEEN '2011-05-16 09:19:00'
                AND '2011-05-16 09:21:00'
) maxrt
WHERE runTime =(SELECT MIN( runTime )
                FROM Check_in
                WHERE runTime BETWEEN '2011-05-16 09:19:00'
                AND '2011-05-16 09:21:00')

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Attend Free Webinar
dolan2go

Author

Commented:
@kdo,

Is there a typo in your suggestion? I can't see lines 3 & 4 working.

When I run it, it returns

#1305 - FUNCTION site5_flifo.MIN does not exist
Kent OlsenDBA

Commented:

That's really two possible solutions.  :)

Run the top half or the bottom half.

dolan2go

Author

Commented:
OK. Running the top half.

The data gets crossed. The result has the 'Num' of one row and the 'fltNum' of another, combined into ONE result row. ?? This is really confusing.

Still working on the 2nd half. I'm not following the logic using column names MaxrunTime in rows 11, 14, 18 above.
dolan2go

Author

Commented:
Kent,

An explanation might have helped me here.

The second (of 2) solution began the path to a solution.

A more helpful comment might have been, 'Using nested queries, the innermost being executed first, etc.'

Many thanks for responding.
Kent OlsenDBA

Commented:
Hi dolan,

You had enough of the pieces in your original post that I thought you were just having a "brain cramp".

Apologies for not offering a detailed explanation, too.


Kent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial