GROUP BY question 1

[teach314]

I'm sure I'm missing something obvious, but here's my question.
Consider the table below.

[b]tblData[/b]
  x     y     z
 ---------------
 12     1    821 
 12     2    230
 12     3    614
 12     4    560

 56     1    829 
 56     2    200
 56     3    604
 56     4    588

 42     1    816
 42     2    299
 42     3    621
 42     4    589
etc...

The data comes in sets of 4 records as shown. For each value of y, I'm interested in the record that has the SMALLEST value of z.
If I only had y and z values, I would GROUP BY tblDATA.y and SELECT MIN(tblDATA.z). My problem is that I need the corresponding x value as well.
The output needs to look like...

  x     y     z
 ---------------
 42     1    816 
 56     2    200
 56     3    604
 12     4    560
etc...

Thanks in advance for any help.
Teach314

 

[dhookom]

You could try SQL like:

SELECT tblData.X, tblData.Y, tblData.Z
FROM tblData
WHERE tblData.Z=(SELECT MIN(Z) FROM tblData D WHERE D.Y = tblData.Y)
ORDER BY tblData.Y;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[teach314]

Yes, of course. Thanks, Duane!