SQL String - Return Highest Value

[Sparky Arbuckle]

I am building an app that is looking for the latest model of a
particular product. I am storing these results in a datalist. Below is
what the location and newest model fields look like:

Location - NewestModel

A1 - A
A1 - B
A2 - A
A3 - A
A3 - B
A3 - C
A4 - A
A5 - A
A5 - B
A5 - C
A5 - D
A5 - E

I know that I can do a SELECT MAX(NewestModel) to return the highest,
E, but it would defeat the purpose of what I am trying to do.

The output I am going for is:


A1 - B
A2 - A
A3 - C
A4 - A
A5 - E


This way the output shows what the latest version is at each location.
This is the way that the database is configured (it was like this when
I came on) so changing the layout and stored procedures is not an
option.


Any suggestions?

 

[Guest]

Should be able to group on Location:

select Location, Max(NewestModel)
from [The Table]
group by Location;

Good Luck!

 

[Sparky Arbuckle]

SELECT tblProduct.Location, tblProduct.ProductType,
tblProduct.ProductNumber, Max(tblProduct.NewestModel)
FROM tblProduct
WHERE tblProduct.Location = "A1" AND (tblProduct.ProductType = "CD")
GROUP BY tblProduct.Location;

I am getting an error when I try to run this in Access.

 

[Douglas J. Steele]

Since you have 3 fields without Max in them, you need to have 3 fields in
your GROUP BY clause:

SELECT tblProduct.Location, tblProduct.ProductType,
tblProduct.ProductNumber, Max(tblProduct.NewestModel)
FROM tblProduct
WHERE tblProduct.Location = "A1" AND (tblProduct.ProductType = "CD")
GROUP BY tblProduct.Location, tblProduct.ProductType,
tblProduct.ProductNumber

 

[Sparky Arbuckle]

Thanks Douglas. That worked like a charm.

 

[Sparky Arbuckle]

How would I go about hooking up another table to this and instead of
retrieving the max for NewestModel, have all the revisions to the new
model listed? I'll explain.

A1 - C
A2 - A
A3 - C

I am able to get those results with no problem using the following SQL
statement:

SELECT tblProduct.Location, tblProduct.ProductType,
tblProduct.ProductNumber, Max(tblProduct.NewestModel) As
MaxOfNewestModel FROM tblProduct WHERE tblProduct.Location = "A1" AND
(tblProduct.ProductType = "CD") GROUP BY tblProduct.Location,
tblProduct.ProductType, tblProduct.ProductNumber;

What if I wanted to bring in another table (tblRevision) and achieve a
result that is:

A1 - C - C1
C2
C3
A2 - A - A1
A3 - B - B1
B2
B3
B4

I have tried to simply add tblRevision.RevisionNumber to the SQL from
above but instead of the MAX NewestModel it is retrieving all of them.
Any suggestions?