How to return only the last record in a join

Question

How to return only the last record in a join

I join the tables. I only want to return one record from the join table based on the date field.

Here's a simplified scenario of what I've done so far: http://sqlfiddle.com/#!3/be0cdd/2

My tables:

  CUSTOMER

| CustomerID |
--------------
| 1          |


  PURCHASE

| PurchaseID | CustomerID | ProductID | CreateDate | ArchiveFlag |
------------------------------------------------------------------
| 1          | 1          | 443       | 01-FEB-15  | F           |
| 2          | 1          | 551       | 01-MAR-15  | F           |
| 3          | 1          | 151       | 01-JAN-15  | F           |
| 4          | 1          | 654       | 01-MAY-15  | T           |
| 5          | 1          | 345       | 01-APR-15  | T           |

and here's the request itself:

select *
from customer c
join purchase p
on c.customerid = p.customerid
and p.archiveflag = 'F';

I only want to return the last purchase that is not archived (in this example, purchase ID 2) for each customer.

Ideal way out:

| CustomerID | PurchaseID | CustomerID_2 | ProductID | CreateDate | ArchiveFlag |
|--------------------------------------------------------------------------------
| 1          | 2          | 1            | 551       | 01-MAR-15  | F           |

+3

sql oracle greatest-n-per-group

drc15 Jul 24 15 at 11:56

source to share

7 replies

MT0 · Answer 1 · 2015-07-24T12:33:35+0000

Oracle 12c introduced a row limit suggestion, and you could do (if you only want one result):

SELECT *
FROM   customer c
       INNER JOIN purchase p
       ON ( c.customerid = p.customerid )
WHERE  p.archiveflag = 'F'
ORDER BY
       CreateDate DESC
FETCH FIRST 1 ROW ONLY

In earlier versions, you can:

SQL Fiddle

Oracle 11g R2 schema setup :

create table CUSTOMER(CustomerID INT);
create table PURCHASE(PurchaseID INT, CustomerID INT, ProductID INT, CreateDate date, ArchiveFlag char);

insert into CUSTOMER values(1);
insert into CUSTOMER values(2);

insert into PURCHASE values(1,1,443,'01-FEB-15','F');
insert into PURCHASE values(2,1,551,'01-MAR-15','F');
insert into PURCHASE values(3,1,151,'01-JAN-15','F');
insert into PURCHASE values(4,1,654,'01-MAY-15','T');
insert into PURCHASE values(5,1,345,'01-APR-15','T');
insert into PURCHASE values(6,2,234,'01-MAY-15','T');
insert into PURCHASE values(7,2,134,'01-APR-15','F');
insert into PURCHASE values(8,2,999,'01-JAN-15','F');
insert into PURCHASE values(9,2,724,'07-JUN-15','F');
insert into PURCHASE values(10,2,345,'01-JUN-15','T');

Request 1 - If you only want the latest data for one client :

SELECT *
FROM   (
  SELECT *
  FROM   Purchase
  WHERE  archiveflag = 'F'
  AND    CustomerID = 1
  ORDER BY
         CreateDate DESC
)
WHERE ROWNUM = 1

Results :

| PURCHASEID | CUSTOMERID | PRODUCTID |              CREATEDATE | ARCHIVEFLAG |
|------------|------------|-----------|-------------------------|-------------|
|          2 |          1 |       551 | March, 01 2015 00:00:00 |           F |

Request 2 - If you want to get the latest data for all clients :

SELECT PurchaseID,
       CustomerID,
       ProductID,
       CreateDate,
       ArchiveFlag
FROM   (
  SELECT p.*,
         ROW_NUMBER() OVER ( PARTITION BY p.CustomerID ORDER BY CreateDate DESC ) RN
  FROM   purchase p
  WHERE  ArchiveFlag = 'F'
)
WHERE  RN = 1

Results :

| PURCHASEID | CUSTOMERID | PRODUCTID |              CREATEDATE | ARCHIVEFLAG |
|------------|------------|-----------|-------------------------|-------------|
|          2 |          1 |       551 | March, 01 2015 00:00:00 |           F |
|          9 |          2 |       724 |  June, 07 2015 00:00:00 |           F |

If PURCHASE.CUSTOMERID

is not an empty foreign key associated with CUSTOMER.CUSTOMERID

, then you don't need to join tables (as above).

Gordon linoff · Answer 2 · 2015-07-24T12:05:44+0000

I think you want to use row_number()

:

select *
from customer c join
     (select p.*,
             row_number() over (partition by p.customerid order by p.createdate desc) as seqnum
      from purchase p
      where p.archiveflag = 'F'
     ) p
     on c.customerid = p.customerid and seqnum = 1;

Maulik shah · Answer 3 · 2015-07-24T12:59:19+0000

SQL Fiddle

Setting up a schema

create table CUSTOMER(CustomerID int)
create table PURCHASE(PurchaseID int, CustomerID int, ProductID int, CreateDate date, ArchiveFlag char)

insert into CUSTOMER values(1)
insert into CUSTOMER values(2)

insert into PURCHASE values(1,1,443,'01-FEB-15','F')
insert into PURCHASE values(2,1,551,'01-MAR-15','F')
insert into PURCHASE values(3,1,151,'01-JAN-15','F')
insert into PURCHASE values(4,1,654,'01-MAY-15','T')
insert into PURCHASE values(5,1,345,'01-APR-15','T')
insert into PURCHASE values(6,2,331,'01-FEB-15','T')
insert into PURCHASE values(7,2,298,'01-JUN-15','F')

Request to get the latest expectations for all clients

 select *
 from purchase pa join customer c on c.customerid=pa.customerid
   where pa.archiveflag = 'F'
   and pa.createdate=(select max(createdate) 
                      from purchase pb
                        where pa.customerid=pb.customerid
                        and pb.archiveflag='F')

Output

| PurchaseID | CustomerID | ProductID | CreateDate | ArchiveFlag | CustomerID |
|------------|------------|-----------|------------|-------------|------------|
|          2 |          1 |       551 | 2015-03-01 |           F |          1 |
|          7 |          2 |       298 | 2015-06-01 |           F |          2 |

Ilessa · Answer 4 · 2015-07-24T12:06:37+0000

You can use max date to do something like this in a join:

select *
from customer c
join (
SELECT PurchaseID, CustomerID, ProductID, Max(CreateDate) as MaxCreateDate, ArchiveFlag 
from purchase )p
on c.customerid = p.customerid
and p.archiveflag = 'F';

Mukesh Kalgude · Answer 5 · 2015-07-24T12:01:29+0000

You can use top and order in your query like

select Top 1 *
from customer c
join purchase p
on c.customerid = p.customerid
and p.archiveflag = 'F' 
Order by p.CreateDate Desc;

Pradeep bogati · Answer 6 · 2015-07-24T12:07:45+0000

Just use where where: as it says you need purchase id 2: follow these steps:

SELECT * FROM Select * from customer c join purchase p at c.customerid = p.customerid and p.archiveflag = 'F'; order by CreateDate desc where PurchasedID = 2;

Shahzad Riaz · Answer 7 · 2015-07-24T12:15:56+0000

Try it.

select top 1 *
from customer c
join purchase p
on c.customerid = p.customerid
and p.archiveflag = 'F'
order by CreateDate desc;

How to return only the last record in a join

More articles: