example: table A (delivery_schedule) has 100 records which joins to table B (deliveries made). The relation is 1 to many. If a new schedule record is added, it will not have a delivery made record. I want to select all of the records from table A and the most RECENT delivery from table B.

the syntax is: select A.customerId, A.companyName, A.productName, isNull( max( invoiceDate ), '2014-01-01' ), isNull( quantityReceived, 0 ) from table A left outer join table b on A.customerID = B.customerID and a.productName = b.productName group by A.customerId, A.companyName, A.productName

the SQL is working as expected, but it's not what i want. i've tried TOP and LIMIT without success. is there an easy way to get what i'm looking for?

asked 28 Jan '15, 15:20

Tom%20Mangano's gravatar image

Tom Mangano
587182334
accept rate: 6%


For a discussion of WINDOW and FIRST_VALUE see this blog post: Great Moments In History: Housing Bubble (I cannot code these things without looking at examples :)

The WITH clause is just like CREATE VIEW except it's local to the following SELECT. It simplifies the SELECT by not having to code all that inside the FROM clause as a derived table.

CREATE TABLE a (
   customerID   INTEGER NOT NULL,
   companyName  VARCHAR ( 10 ) NOT NULL,
   productName  VARCHAR ( 10 ) NOT NULL,
   PRIMARY KEY ( customerID, productName ) );

CREATE TABLE b (
   customerID   INTEGER NOT NULL,
   productName  VARCHAR ( 10 ) NOT NULL,
   invoiceDate       DATE NOT NULL,
   quantityReceived  INTEGER NOT NULL,
   PRIMARY KEY ( customerID, productName, invoiceDate ),
   FOREIGN KEY fa ( customerID, productName ) REFERENCES a ( customerID, productName ) );

INSERT a VALUES ( 1, 'aaa', 'product1' );
INSERT b VALUES ( 1, 'product1', '2015-01-01', 10 );
INSERT b VALUES ( 1, 'product1', '2015-01-02', 20 );
INSERT a VALUES ( 1, 'aaa', 'product2' );
INSERT b VALUES ( 1, 'product2', '2014-11-01', 100 );
INSERT b VALUES ( 1, 'product2', '2014-12-01', 200 );
INSERT a VALUES ( 1, 'aaa', 'product3' );
COMMIT;

WITH latest_b AS (
SELECT DISTINCT
       customerID,
       productName,
       FIRST_VALUE ( invoiceDate )      OVER date_window AS latest_invoiceDate,
       FIRST_VALUE ( quantityReceived ) OVER date_window AS latest_quantityReceived 
  FROM b
WINDOW date_window AS (
               PARTITION BY customerID, productName
               ORDER BY invoiceDate DESC
               RANGE BETWEEN UNBOUNDED PRECEDING 
                         AND UNBOUNDED FOLLOWING ) 
)
SELECT a.customerID,
       a.companyName,
       a.productName,
       COALESCE ( latest_b.latest_invoiceDate, CURRENT DATE ) AS invoiceDate,
       COALESCE ( latest_b.latest_quantityReceived, 0 )       AS quantityReceived
  FROM a LEFT OUTER JOIN latest_b
          ON a.customerID  = latest_b.customerID
         AND a.productName = latest_b.productName
 ORDER BY a.customerID,
       a.productName;

 customerID companyName productName invoiceDate quantityReceived 
----------- ----------- ----------- ----------- ---------------- 
          1 aaa         product1    2015-01-02                20 
          1 aaa         product2    2014-12-01               200 
          1 aaa         product3    2015-01-28                 0 
permanent link

answered 28 Jan '15, 17:17

Breck%20Carter's gravatar image

Breck Carter
27.0k424581830
accept rate: 21%

This is a clunkier way of doing it than Breck's, avoiding the OLAP functionality. BUT, while this may seem simpler if you aren't familiar with WINDOW, PARTITION BY and their friends, in real world examples it soon gets very messy! So the moral is, it's well worthwhile getting to grips with the OLAP as it is extraordinary powerful.

There are quite a few things you would need to resolve in your example for real eg two deliveries on the same day. However for what it's worth heres's my fudge:

create table A(PK int default autoincrement primary key, CustomerID int, CompanyName char(20), ProductName char(20));

create table B(PK2 int default autoincrement primary key, CustomerID int, ProductName char(20), InvoiceDate date, QuantityReceived int)  ;

insert into A(CustomerID, CompanyName, ProductName) values (1,'Co A','Prod A');
insert into A(CustomerID, CompanyName, ProductName) values (1,'Co A','Prod B');
insert into A(CustomerID, CompanyName, ProductName) values (1,'Co A','Prod C');
insert into A(CustomerID, CompanyName, ProductName) values (2,'Co B','Prod A');
insert into A(CustomerID, CompanyName, ProductName) values (2,'Co B','Prod B');
insert into A(CustomerID, CompanyName, ProductName) values (3,'Co C','Prod B');
insert into A(CustomerID, CompanyName, ProductName) values (4,'Co D','Prod B');

insert into B(CustomerID , ProductName, InvoiceDate, QuantityReceived) values (1,'Prod A','2015-01-15',25);
insert into B(CustomerID , ProductName, InvoiceDate, QuantityReceived) values (1,'Prod A','2015-01-25',50);
insert into B(CustomerID , ProductName, InvoiceDate, QuantityReceived) values (1,'Prod B','2015-01-06',30);
insert into B(CustomerID , ProductName, InvoiceDate, QuantityReceived) values (1,'Prod A','2015-01-17',25);
insert into B(CustomerID , ProductName, InvoiceDate, QuantityReceived) values (2,'Prod A','2015-01-15',225);
insert into B(CustomerID , ProductName, InvoiceDate, QuantityReceived) values (2,'Prod A','2014-01-15',7725);

select A.PK, a.CustomerID, a.CompanyName, a.ProductName, B.InvoiceDate, B.QuantityReceived  from A left outer join B on A.CustomerID  = B.CustomerID and A.ProductName = B.ProductName
where b.InvoiceDate is null OR (B.InvoiceDate = (select max(Z.InvoiceDate) from B Z where Z.CustomerID = B.CustomerID and Z.ProductName=B.ProductName)) 
 order by A.CustomerID, A.ProductName

PK CustomerID CompanyName ProductName InvoiceDate QuantityReceived
== ========== =========== =========== =========== ================
21 1          'Co A'      'Prod A'    '2015-01-25' 50
22 1          'Co A'      'Prod B'    '2015-01-06' 30
23 1          'Co A'      'Prod C'    <NULL>       <NULL>
24 2          'Co B'      'Prod A'    '2015-01-15' 225
25 2          'Co B'      'Prod B'    <NULL>       <NULL>
26 3          'Co C'      'Prod B'    <NULL>       <NULL>
27 4          'Co D'      'Prod B'    <NULL>       <NULL>
permanent link

answered 28 Jan '15, 17:35

Justin%20Willey's gravatar image

Justin Willey
6.5k105135203
accept rate: 21%

edited 28 Jan '15, 17:41

In addition to Justin's example, here's a similar query. It uses a group-by derived table to get the "latest delivery per customer/product instead of a sub select in the WHERE clause. I can't tell whether this is easier to use...

Note: The missing WHERE clause helps to prevent the typical error that the LEFT JOIN is incidentally turned into an INNER JOIN by suppressing NULL values. Of course Justin has avoided that pitfall by explicitly using "b.InvoiceDate is null OR " in the WHERE clause. - Similarily, in this query it's important to do the B INNER JOIN Z before the outer join to A.

select A.PK, a.CustomerID, a.CompanyName, a.ProductName, B.InvoiceDate, B.QuantityReceived
from A left outer join
    (B  inner join
          (select CustomerID, ProductName, max(InvoiceDate) as MaxInvoiceDate
           from B group by CustomerID, ProductName) Z
        on    B.CustomerID = Z.CustomerID and B.ProductName = Z.ProductName
          and B.InvoiceDate = Z.MaxInvoiceDate)
   on A.CustomerID  = B.CustomerID and A.ProductName = B.ProductName
order by A.CustomerID, A.ProductName

The result set is identical:

PK,CustomerID,CompanyName,ProductName,InvoiceDate,QuantityReceived
1,1,'Co A','Prod A','2015-01-25',50
2,1,'Co A','Prod B','2015-01-06',30
3,1,'Co A','Prod C',,
4,2,'Co B','Prod A','2015-01-15',225
5,2,'Co B','Prod B',,
6,3,'Co C','Prod B',,
7,4,'Co D','Prod B',,

I'm still getting used to use derived queries to calculate the "max values" (here Z) and to then join that with the "real data" (here B) to get the rows with the maximum values per ID.

permanent link

answered 29 Jan '15, 08:17

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

edited 29 Jan '15, 08:27

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×6

question asked: 28 Jan '15, 15:20

question was seen: 394 times

last updated: 29 Jan '15, 08:27