CODEWITHSUNDEEP
sqlms-access
AndreasMuurholm
AndreasMuurholm

Reputation: 1

SQL - get first match of Where criteria - MS Access

I have a a dataset of sales looking something like this

Product  sold.date
apple    01/jan/2012
banana   01/feb/2012
orange   01/mar/2012

in the same periode there will be campaigns i.e.

Campaign name      start           end
campaign1        01/jan/2012     15/feb/2012
campaign2        01/feb/2012     01/mar/2012

In SQL, how do I get just 1 campaign in which a product was sold? that is, banana is sold in campaign1 OR campaign2, not counted as a sale twice.

It does not matter which campaign the sales is counted on. There will be multiple rows of sales that will overlap more then 1 campaign.

Any help would be great...

Upvotes: 0

Views: 912

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

Get 1 campaign for 1 product

You did not name your RDBMS. In PostgreSQL or MySQL you could use LIMIT 1.

SELECT p.Product, c.Camgaign
FROM   p
LEFT   JOIN c ON p.sold_date BETWEEN c.start AND c.end
WHERE  p.Product = 'banana'
-- ORDER BY <something>
LIMIT  1;

I use LEFT [OUTER] JOIN to return the 'banana' row, even if there is no matching campaign. Campaign would be NULL in that case.

Use ORDER BY to select a campaign by some available criteria instead of getting just an arbitrary one that qualifies.

In MS SQL you would use SELECT TOP n.
In Oracle WHERE rownum <= 1;

Get 1 campaign for all products

WITH x AS (
    SELECT p.Product, c.Camgaign
          ,row_number() OVER (PARTITION BY p.Product) AS rn
    FROM   p
    LEFT   JOIN c ON p.sold_date BETWEEN c.start AND c.end
    WHERE  p.Product = 'banana'
    )
SELECT Product, Camgaign
FROM   x
WHERE  rn = 1
   OR  rn IS NULL;  -- to include prod. without campaign

Tested in PostgreSQL, works in MS SQL Server or Oracle.
Does not work in MySQL which support neither CTEs nor window functions.

Upvotes: 1

Related Questions