MAX() & all columns

[Guest]

I use SQL once a year so struggle on the simplest tings.

I want to retrieve the entire row/record for a series of the max values.
Its easiest enough to get the values to display is they are in the GROUP BY
clause but its returning incorrect results. I think I need a statement
similar to:

Select MAX(SaleDate), Item, Salesman From Log Group By Item

....but it won't display the Salesman column because its not in one of the
aggregate functions. I want the Salesman value from the record that has the
most recent sales for each item.

The data might look like

SaleDate Item Salesman
1/1/01 Candy Bob
1/5/01 Candy Sally
1/5/01 Toy Sally
1/6/01 Toy Bob

I need the query to return (one row per item):
SaleDate Item Salesman
1/5/01 Candy Sally
1/6/01 Toay Bob

 

[giorgio rancati]

Hi, Rick E

----
SELECT Max(SaleDate) AS MaxOfSaleDate,
Item,
Last(Salesman) AS LastOfSalesman
FROM (SELECT TOP 100 PERCENT *
FROM MyTable
ORDER BY Item,SaleDate) AS DrvTbl
GROUP BY Item;
----

do not forget *TOP 100 PERCENT* in the derived table otherwise the function
Last () returns the last value introduced in MyTable and do not the last of
DrvTbl

Bye
Giorgio

 

[Marshall Barton]

I use SQL once a year so struggle on the simplest tings.

I want to retrieve the entire row/record for a series of the max values.
Its easiest enough to get the values to display is they are in the GROUP BY
clause but its returning incorrect results. I think I need a statement
similar to:

Select MAX(SaleDate), Item, Salesman From Log Group By Item

...but it won't display the Salesman column because its not in one of the
aggregate functions. I want the Salesman value from the record that has the
most recent sales for each item.

The data might look like

SaleDate Item Salesman
1/1/01 Candy Bob
1/5/01 Candy Sally
1/5/01 Toy Sally
1/6/01 Toy Bob

I need the query to return (one row per item):
SaleDate Item Salesman
1/5/01 Candy Sally
1/6/01 Toay Bob

Not all that simple.

I think this is another way to do that:

SELECT SaleDate, Item, Salesman
FROM Log INNER JOIN
[SELECT Max(X.SaleDate), X.Item
FROM Log As X
GROUP BY X.Item]. As Y
ON Log.SaleDate = Y.SaleDate
And Log.Item = Y.Item

 

[John Spencer (MVP)]

Easiest solution uses two queries.

QueryMaxDates
SELECT Max(SaleDate) as LastSold, Item
FROM Log
GROUP BY Item

Now using that saved query as a table.

SELECT Log.SaleDate, Log.Item, Log.Salesman
FROM LOG INNER JOIN QueryMaxDates as Q
ON Log.SaleDate = Q.LastSold and
Log.Item = Q.Item

It can all be done in one query as the following, although this might be a bit
tougher to follow.
SELECT Log.SaleDate, Log.Item, Log.Salesman
FROM LOG INNER JOIN
[SELECT Max(SaleDate) as LastSold, Item
FROM Log
GROUP BY Item]. as Q

 

[Guest]

You can create two queries
1. First query will return the Item With the max date
SELECT Max(MyTable.SaleDate) AS MaxSaleDate , MyTable.Item
FROM MyTable
GROUP BY MyTable.Item

2. The second query will join the query above with the table, joining Item
to Item, and MaxDate to date, and display all the records from the table

 

[Guest]

Wow, thanks to all for the posts. Apparently this query wasn't as easy as I
thought (at least for me). i apreciate everyone's help.