Extract Values from records with max/min values

Question

I posted a question earlier and got a great answer but realize I went about my logic the wrong way. Given a table like this:

Name|ValueA|ValueB1|ValueB2
Bob |     1|    200|    205
Bob |     2|    500|    625
Bob |     7|    450|    850
Bob |     3|    644|    125
Ann |     4|    120|    120
Ann |     8|    451|    191
Ann |     9|    145|    982

I originally was trying to get the max/min values for each unique names and ended up with

Create TableA as (Select Name,Max(ValueA),Min(ValueA),Max(ValueB1,Max(ValueB2) Group by Name)

but this gave me (naturally) the high/low for each of A, B1, B2 e.g.

• Bob|1|7|200|644|205|850

What I am looking for are the B1 and B2 values for the each of the lowest and highest A values per unique name in other words in the above I need

• Bob|1|7|200|205|450|850
• Ann|4|9|120|120|145|982

which gives me the high and low A values and the B1 and B2 for values contained in the high and low A Value records.

(This is NOT a duplicate question. My last question asked and answered how to pull the high and low values for three different fields for a given unique name into a new table. This turns out to not be what I needed although the first question successfully was answered (and marked as such). What I need are the values for two fields from the high and low values of another field for a given name. If you look at the question you will see this is so and the solutions are in fact different)

Answer 1

SELECT tmin.Name, tmin.ValueA, tmax.ValueA, 
    tmin.ValueB1, tmin.ValueB2, tmax.ValueB1, tmax.ValueB2
FROM (
  SELECT Name, MAX(ValueA) AS ValueAMax, MIN(ValueA) AS ValueAMin
  FROM `foo`
  GROUP BY Name
) AS t
JOIN `foo` AS tmin ON t.Name = tmin.Name AND t.ValueAMin = tmin.ValueA
JOIN `foo` AS tmax ON t.Name = tmax.Name AND t.ValueAMax = tmax.ValueA;

Answer 2

end your query with:

group by Name