[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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?
0
bfuchs
Asked:
bfuchs
  • 6
  • 6
1 Solution
 
PortletPaulCommented:
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
 
PortletPaulCommented:
-- 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
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
 
PortletPaulCommented:
:) glad that part is working. Cheers, Paul
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now