Return Highest Record (SQL - Access)

[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?

[Curt_C [MVP]]

Sparky Arbuckle wrote:

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?

Its not an ASP.NET thing, its a tSql thing...
I think you are looking for the GROUP BY clause

--
Curt Christianson
site: http://www.darkfalz.com
blog: http://blog.darkfalz.com

[Paul Clement]

On 17 Aug 2005 12:17:34 -0700, "Sparky Arbuckle" <tw*@secureroot.com> wrote:
¤ 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?

Example of what Curt referred to:

http://www.techonthenet.com/sql/group_by.php
Paul
~~~~
Microsoft MVP (Visual Basic)

[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?

[Paul Clement]

On 18 Aug 2005 08:48:43 -0700, "Sparky Arbuckle" <tw*@secureroot.com> wrote:

¤ 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?

Sounds like you need to do a JOIN. You may want to repost this question to
microsoft.public.framework.ado.net.
Paul
~~~~
Microsoft MVP (Visual Basic)