Return Highest Record (SQL - Access)

S

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

Curt_C [MVP]

Sparky said:
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
 
P

Paul Clement

¤ 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)
 
S

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

Paul Clement

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top