Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

GROUP BY question 1

Status
Not open for further replies.

teach314

Technical User
Jul 29, 2011
183
CA
I'm sure I'm missing something obvious, but here's my question.
Consider the table below.

Code:
[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...

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

Thanks in advance for any help.
Teach314
 
You could try SQL like:

SQL:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top