VB6 SQL Query - Selecting max within a group and other columns

[jurban]

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.

[techgnome]

Almost... but it's not quite what you need... what you need to do is like you did in the first query (without the order by).... then join it back to the source table.... sounds complicated, but it isn't .... done this plenty of times before...

SELECT d.Name, d.Age, d.Height
FROM tblData d
INNER JOIN (SELECT Age, MAX(Height) AS MaxHeight FROM tblData GROUP BY Age) A
  ON d.Age = A.Age AND d.Height = A.MaxHeight

The only problem is if you have two people with the same age and height.

-tg

[FunkyDexter]

The only problem is if you have two people with the same age and height.

If you do have that scenario and you only want it to pick one you can introduce a 'top 1' to get around it:-

SELECT d.Name, d.Age, d.Height
FROM tblData d
INNER JOIN (Top 1 SELECT Age, MAX(Height) AS MaxHeight FROM tblData GROUP BY Age) A
  ON d.Age = A.Age AND d.Height = A.MaxHeight

You can't predict which of the 2 matches will be returned but the idea can be extended with an 'order by' if you have a way of defining which should get returned.


edit> Actually, scratch that. I don't think it'll work when using a joing like that. I usually use sub queries to handle this situation and there you can use it.

[jurban]

Almost... but it's not quite what you need... what you need to do is like you did in the first query (without the order by).... then join it back to the source table.... sounds complicated, but it isn't .... done this plenty of times before...

[....]

The only problem is if you have two people with the same age and height.

-tg

Thanks to both of you -- I'll give it a try on Monday and post again if I have problems. I think in my case there will never be two people with the same age and height (I actually used that as a simplified example of the database I'm working with).

[jurban]

Almost... but it's not quite what you need... what you need to do is like you did in the first query (without the order by).... then join it back to the source table.... sounds complicated, but it isn't .... done this plenty of times before...

SELECT d.Name, d.Age, d.Height
FROM tblData d
INNER JOIN (SELECT Age, MAX(Height) AS MaxHeight FROM tblData GROUP BY Age) A
  ON d.Age = A.Age AND d.Height = A.MaxHeight

The only problem is if you have two people with the same age and height.

-tg

Thanks a lot -- it seems to work, and it's fast. The only problem is that it complained about the Age column being ambiguous, so I used an alias for that column in the SELECT statement that is the argument of the INNER JOIN:

SELECT d.Name, d.Age, d.Height
FROM tblData d
INNER JOIN (SELECT Age AS TmpAge, MAX(Height) AS MaxHeight FROM tblData GROUP BY TmpAge) A
  ON (d.Age = A.TmpAge AND d.Height = A.MaxHeight) ORDER BY d.Age

[jurban]

Oops, that should be:

SELECT d.Name, d.Age, d.Height
FROM tblData d
INNER JOIN (SELECT Age AS TmpAge, MAX(Height) AS MaxHeight FROM tblData GROUP BY Age) A
  ON (d.Age = A.TmpAge AND d.Height = A.MaxHeight) ORDER BY d.Age

[/QUOTE]

[techgnome]

it complained about the Age column being ambiguous, so I used an alias for that column in the SELECT statement that is the argument of the INNER JOIN:

Strange.... I've never had that as an issue before... but if it works, it works....
Ah... wait... this is in Access isn't it? If so, then that would explain it.

-tg

[jurban]

Strange.... I've never had that as an issue before... but if it works, it works....
Ah... wait... this is in Access isn't it? If so, then that would explain it.

-tg

Ah, yes, I forgot to mention that I'm reading the data out of an Access database.