query design help needed!

Hi Experts,
What is the simplest way to create a group by query that returns the last order date per customer, and also includes the rest of the fields of the order table for that returned record, besides the CustomerID and OrderDate?
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
which database is this for?
Access?
SQL Server?

 need to know if you are able to use the row_number() function?
0
bfuchsAuthor Commented:
Hi,
actually i need this in both versions, as i have two applications one is a pure access based, the other is sql BE and Access FE.
0
PortletPaulfreelancerCommented:
-- generic group by query that returns the last order date per customer
SELECT
      *
FROM YourTable AS data
      INNER JOIN (
                  SELECT
                        customerid
                        max (order_date) AS order_date
                  FROM YourTable
                  GROUP BY
                        customerid
            ) AS md
                  ON data.customerid = md.customerid
                        AND order_date = md.order_date
;

Open in new window

{+ edit}
In the vast majority of situations this will meet your need, however this particular technique cannot guarantee a single record per customer IF there are 2 or more orders from the same customer with the same date/time and that date/time is the latest date/time.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bfuchsAuthor Commented:
Hi PortletPaul,

Thanks for the suggestion, I am waiting for a chance to integrate it with my application and have users test it, will keep you posted.

just wonder if that row number approach you mentioned above would work faster for the sql application?
0
PortletPaulfreelancerCommented:
row_number() might work faster, or it might not. I cannot really predict that - but it is a very useful technique when it is available to you.
0
bfuchsAuthor Commented:
Hi PortletPaul,

I realized that there are multiple orders per day per customer, how is it possible to modify that query to return only the last entry per day for each customer?
(to determine that its possible to look at the ID or DateEntered)
0
PortletPaulfreelancerCommented:
Without any way to test this all I can propose is that you add MAX(ID) into the subquery and try joining that way. I don't have enough current knowledge about Access to offer other alternatives.
SELECT
      *
FROM YourTable AS data
      INNER JOIN (
                  SELECT
                        customerid
                        max (ID) AS id
                  FROM YourTable
                  GROUP BY
                        customerid
            ) AS md
                  ON data.customerid = md.customerid
                        AND data.id = md.id
;

Open in new window

Here is what the row_number() approach looks like:
SELECT
      *
FROM (
            SELECT
                  *
                , ROW_NUMBER() OVER (PARTITION BY customerid
                  ORDER BY order_date DESC, ID DESC) AS rn --<< or DateEntered if you prefer
            FROM YourTable AS data
      ) AS sq
WHERE rn = 1

Open in new window

0
bfuchsAuthor Commented:
Hi,

I cant see how the first suggestion should work, as I need  to see the last order date per customer and from that date only the last record, while this query will return the last record entered per customer, which is not necessary the same.

However since I am working currently to make the sql app work, I copied what you suggested with the row_number into my application, and will have users testing it tom and let you know.

Thanks,
Ben
0
bfuchsAuthor Commented:
Hi PortletPaul,
we are in middle of testing the row_number suggestion, just have some questions:

1- if I want to add another level of grouping by, for example, last order date per customer per vendor, where do I add that, next to the partition portion?
2- also when trying to add a where clause, where do I enter that?
3- in general, can you point to a place where I can get a simple explanation how this row_number magic works?

Thanks,
Ben
0
PortletPaulfreelancerCommented:
Ben,

1 - not sure. could be partition by, but might be order by (a date field is more likely to be for ordering)
2 - as normal, no change to placement of where clause, except that you must "nest" the row_number() as I showed earlier before you can filter by it.
SELECT
      *
FROM (
            SELECT
                  *
                , ROW_NUMBER() OVER (PARTITION BY customerid
                  ORDER BY order_date DESC, ID DESC) AS rn --<< or DateEntered if you prefer
            FROM YourTable AS data

-- can be here

      ) AS sq
WHERE rn = 1

-- and / or here

Open in new window

3 - see below

Books On Line (BOL) references for this are:

ROW_NUMBER()
OVER()

I suggest you start with OVER() that determines the partitioning and ordering
:: a partition in this context is just a set of rows

For ROW_NUMBER() the combination of partition and order defines when to re-start numbering at 1

row_number() by itself just spits out a continuous stream of integers starting at 1 incrementing by 1
(until an ordered partition boundary is reached, when it restarts at 1)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thanks PortletPaul, this magic worked!!

will worry about the access solution another time, at the moment I am more then happy that this works.
0
PortletPaulfreelancerCommented:
:) glad that part is working. Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.