CODEWITHSUNDEEP
phpmysql
user2869163
user2869163

Reputation: 83

Need to get Max/Min and all the rows in-between with a SELECT in mysql

I am trying to get the Maximum and Minimum values and have those assigned as separate variables in php plus select all the other rows in between and have that as another variable then populate a dropdown list with the retrieved information.

SELECT z_depth AS z_depth FROM elements
UNION
SELECT MAX(z_depth) AS MAX FROM elements
UNION
SELECT MIN(z_depth) AS MIN FROM elements

The problem is that it is not returning z_depth AS z_depth and MAX as MAX etc, it only shows as z_depth. If somebody would not mind pointing out a solution or possibly an even better way to do it I would be very grateful.

Upvotes: 2

Views: 143

Answers (3)

GPHemsley
GPHemsley

Reputation: 595

A simple SQL query:

SELECT z_depth
FROM elements
ORDER BY z_depth ASC

Read that into an array called $results and then do:

$min = min($results);
$max = max($results);

That should get you what you want.

Upvotes: 1

user4804138
user4804138

Reputation: 112

Are you trying something like this

SELECT z_depth FROM elements WHERE z_depth >= ( SELECT MIN(z_depth) FROM elements ) && z_depth <= ( SELECT MAX(z_depth) FROM elements ) ORDER BY z_depth ASC

Then you can always save the first and the last rows as min and max

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

You can create a second column in the query which will identify the nature of the record ("max", "min", "other"):

SELECT z_depth AS z_depth,  'Other' AS Type FROM elements
UNION
SELECT MAX(z_depth) AS MAX, 'Max'   AS Type FROM elements
UNION
SELECT MIN(z_depth) AS MIN, 'Min'   AS Type FROM elements

The trouble with just doing a blind series of UNION queries as you had it is that you were ending up with a single column without knowing where any particular records might be.

Upvotes: 0

Related Questions