3 Replies Latest reply on Jul 17, 2016 11:08 AM by siplus

    SQL Question is FMPA14.




      I am trying to generate some sql that will return the last item in for each grouped set. I'm sure that I have not made myself clear, so I will give and example.


      I have a table ORDERS and I wish to get the last ORDER for each customer.


      I have written the following, and it works.


      SELECT OrderCustomer, MAX(OrderDate)

        FROM Orders

        GROUP BY OrderCustomer


      With me so far? However, I now need to bring out in the returned data the OrderContactName (text field) for the returned OrderDate.

      I can't put this in as, because it is not a grouped field, it must have a 'function' applied to the data - such as MAX, MIN, COUNT etc.

      I don't want any of these 'functions'.

      I just want the OrderContactName that is associated with the returned record per OrderCustomer.


      Any thoughts or solutions, All are welcome.





        • 1. Re: SQL Question is FMPA14.

          What happens if a customer places more than one order in the same day ?


          I would go with max(OrderID) instead, get a list of ClientID's and feed it to another SQL - after preparing it with "( " & substitute(.......; ¶; ", ") & " )" - etc - see attached example.

          • 2. Re: SQL Question is FMPA14.

            Your answer, siplus, has certainly given me food for thought.

            I was, however, trying to get both fields (the last date AND the person associated with that order) coming out at the same time in the query. I probably didn't make that clear.

            I guess that I could change the last SELECT statement in your attached example to join with the Orders table.


            Good question about the fact that the customer might place more than one order in a day. The example I chose to illustrate is not an exact copy of my problem. I just invented it to ask a point. My stumbling block was the issue that you can't select 2 or more pieces of data from a record when you are using GROUP BY. Sure you can use MAX, MIN etc, but I can't use that in this case.

            • 3. Re: SQL Question is FMPA14.

              Well it sure helps to ask the exact question you want answered, and bonus points are awarded if you mention what you are going to do with the data you want to gather, because it might turn out that a report created without ExecuteSQL is what you actually need.