MSSQL of MS Access "LAST"

Hi Folks,

I am translating an Access query to SQL but having a hard time!

The LAST function works fine in Access but barfs in MSSQL. Is there an equivalent? Here is a snip of the code that works great in Access:

SELECT DISTINCT Last(Customers.custLastName) AS LAST1, Last(Customers.custFirstName) AS FIRST1, Last(Customers.custAddr1) AS SHIP1, etc etc.

The output just takes the last of a bunch of almost identical entries, ie only one customer per line even though they make many orders.

DISTINCT still seems to grab all of the entries in the db. Do I need to expand on this?

Thanks!try using MIN() instead

rudy|||Hi Rudy,

Thanks for helping with this, but I have a new problem now. If I use the MIN() function, I get one result per name (good) but some fields do not match up (bad). Here's what I mean:

The only way to get a working result is to use this query

SELECT DISTINCT MIN(sfCustomers.custLastName) AS LAST1, MIN(sfCustomers.custFirstName) AS FIRST1, MIN(sfCustomers.custAddr1) AS SHIP1, MIN(sfCustomers.custAddr2) AS SHIP2, MIN(sfCustomers.custCity) AS CITY1, MIN(sfCustomers.custState) AS PROV, MIN(sfCustomers.custZip) AS ZIP1, MIN(sfCustomers.custPhone) AS PHONE, MIN(sfCustomers.custCountry) AS CONTACT1, MIN(sfCustomers.custEmail) AS NOTES, MIN(sfCustomers.custLastName) AS [LAST], MIN(sfCustomers.custFirstName) AS [FIRST], MIN(sfCustomers.custAddr1) AS ADD1, MIN(sfCustomers.custAddr2) AS ADD2, MIN(sfCustomers.custCity) AS CITY, MIN(sfCustomers.custZip) AS ZIP, MIN(sfCustomers.custCountry) AS CONTACT, MIN(sfOrders.orderDate) AS [DATE], MIN(sfOrderDetails.odrdtOrderId) AS IDENT, MIN(sfOrderDetails.odrdtProductID) AS ITEM, MIN(sfOrderDetails.odrdtQuantity) AS Q, MIN(sfProducts.prodName) AS [DESC], MIN(sfProducts.prodPrice) AS PRICE
FROM sfCustomers, sfOrderDetails, sfProducts, sfOrders
WHERE (((sfOrders.orderCustId)=sfCustomers.custID) And ((sfOrderDetails.odrdtOrderId)=sfOrders.OrderID) And ((sfOrderDetails.odrdtProductID)=sfProducts.prodID ))
GROUP BY sfOrders.orderCustId;

However, the fields aren't from the same record! If I use just a MIN() on the first column, I get the error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'sfCustomers.custFirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'm confused! How do I get just the one record, but with the full info from the one record?

Thanks by the way, I'm really in a bind here!|||start with the GROUP BY

you are grouping by orders, i.e. you will get one row per order

assuming each order belongs to only one customer, you can bring in customer info along with order info and still get one row per order

but what about products ordered? presumably there can be multiple products on an order, so which (single) product did you want to pick? remember, you are asking for only one row per order

rudy