Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    24

    Question Unanswered: MSSQL equivalent 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!
    Last edited by DataWho; 10-17-02 at 14:27.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try using MIN() instead

    rudy

  3. #3
    Join Date
    Oct 2002
    Posts
    24

    Hmm much better but still not complete!

    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!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •