Hello,

I am trying to populate an ADODB Recordset within a VB6 project using an SQL query, but I am encountering difficulties. The simplified version of my problem is as follows. I have a table (tblData) that contains the names, ages, and heights of people, along with other data. I want to populate a recordset with rows that list the person who has the maximum height for each age listed in the table. The relevant columns in the table are called 'Name', 'Age', and 'Height'.

First, I tried to use GROUP BY to select the maximum height for each age:

SELECT Age, MAX(Height) AS MaxHeight FROM tblData GROUP BY Age ORDER BY Age

This worked fine, but when I tried to add the 'Name' column to the SELECT statement, I found that I also had to add the 'Name' column to the GROUP BY statement, which needs to include all the non-aggregate columns. When I did this, I was not able to get the result that I wanted (many extra rows were included in the result of the query).

I then tried to select all three columns using a correlated subquery:

SELECT Name, Age, Height FROM tblData AS tblTempData WHERE Height IN (SELECT MAX(Height) FROM tblData WHERE Age = tblTempData.Age) ORDER BY Age

This actually produces the result that I want, but it is very slow. Does anyone know how I can solve this problem faster, perhaps using a JOIN statement?

Thank you.