Selecting distinct values

[JensB]

Hi Ng

I got a table containing headers for quotations, with the columns QuoteDate,
QuoteNo, TrackNo, TotalQuoteAmount and other data about the customer.
I want to get the total value quoted for all customers for a given period of
time for production planning purpose.
As we all know, customers change their mind, therefore the table contain
revised quotes.
To avoid more values for the same case are added to the total and giving a
false picture,
I am only interested to get the latest of the revised quotes in my query.
The trackNo column contain a the numeric value of the quote,
this means, if we have the quotes 1000, 1000-a , 1000b and 1000c then the
trackNo will be 1000 for all 4 rows.
If I then use SELECT DISTINCT TrackNo I get correct number of rows, but my
question is:
How do I for sure get the latest (newest) quote 1000c?
I tried with MAX(QuoteDate), but get an error.

any help would be great

Jens

 

[Guest]

Here is the sql for selecting the most recent Order information from each
Customer in the Northwind sample database. You should be able to use this to
pattern your query.

SELECT Orders.*
FROM Orders
WHERE (((Orders.OrderID) In (SELECT TOP 1 OrderID FROM Orders O WHERE
O.CustomerID = Orders.CustomerID ORDER BY OrderDate Desc)))
ORDER BY Orders.CustomerID, Orders.OrderDate;

 

[JensB]

Hi Duane
Thx for feedback

Your SQL only give me 1 quote for each customer.
I need more than the most recent.
I need all quotes, but only the latest quote, if more than one quote have
been made for the same case.

JensB

 

[Guest]

I am confused by "I need all quotes, but only the latest quote, if more than
one quote have been made for the same case."

Can you take the time to provide some sample records with significant fields
and how you would expect these to get displayed in your query?

 

[JensB]

Hi Duane

This is some samples rows in the table quotehead:
CustNo QuoteNo TrackNo and a lot of other colums......
200 10 10
200 10-a 10
200 10-b 10
300 20 20
400 30 30
400 30-a 30

I want the latest QuoteNo from each group of trackNo
Result should be like this:
200 10-b 10
300 20 20
400 30-a 30

Hope this explains it
\Jens

 

[Guest]

Do you have a primary key on the table? Assuming you do and its name is ID,
try:

SELECT quotehead.*
FROM quotehead
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo ORDER BY
QuoteNo Desc)))
ORDER BY quotehead.CustNo, quotehead.TrackNo;

 

[JensB]

Hi Duane
I was just sitting with my querybox open, and pasting your SQL into the box,
it worked perfect. This is great.
Thanks for your help and support

\Jens