Avatar of dolan2go
dolan2goFlag for United States of America

asked on 

MySQL min() and max()

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
MySQL Server

Avatar of undefined
Last Comment
Kent Olsen
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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

Avatar of dolan2go
dolan2go
Flag of United States of America image

ASKER

@kdo,

Only 1 row.

1st pick MIN(runTime), then of those, pick the MAX(Num) and return that ONE row.
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of dolan2go
dolan2go
Flag of United States of America image

ASKER

@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
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image


That's really two possible solutions.  :)

Run the top half or the bottom half.

Avatar of dolan2go
dolan2go
Flag of United States of America image

ASKER

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.
Avatar of dolan2go
dolan2go
Flag of United States of America image

ASKER

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.
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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
MySQL Server
MySQL Server

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

49K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo