CODEWITHSUNDEEP
sqlsql-server
Alex Lemesios
Alex Lemesios

Reputation: 532

SQL Server : select only last record per customer from a join query

Assume I have these 3 tables :

Customer type 1

Customer type 2

enter image description here

The first 2 tables define customers of different types ,i.e second table has other columns which are not included in table 1 i just left them the same to save complexity.

The third table defines orders for both types of customers . Each customer has more than one orders

I want to select the last order for every customer, i.e the order with order_id 4 for customer 1 which was created on 23/12/2016 and the order with order_id 5 for customer 2 which was created on 26/12/2016

I tried something like this : 

select * 
from customertype1 
left join order on order.customer_id = customertype1.customer_id 
order by order_id desc;

But this gives me multiple records for every customer, as I have stated above I want only the last order for every customertype1.

Upvotes: 2

Views: 3428

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

If you want the last order for each customer, then you only need the orders table:

select o.*
from (select o.*,
             row_number() over (partition by customer_id order by datecreated desc) as seqnum
      from orders o
     ) o
where seqnum = 1;

If you want to include all customers, then you need to combine the two tables. Assuming they are mutually exclusive:

with c as (
      select customer_id from customers1 union all
      select customer_id from customers2
     )
select o.*
from c left join
     (select o.*,
             row_number() over (partition by customer_id order by datecreated desc) as seqnum
      from orders o
     ) o
     on c.customer_id = o.customer_id and seqnum = 1;

A note about your data structure: You should have one table for all customers. You can then define a foreign key constraint between orders and customers. For the additional columns, you can have additional tables for the different types of customers.

Upvotes: 3

Dan Bracuk
Dan Bracuk

Reputation: 20804

This is the general idea. You can work out the details.

with customers as
(select customer_id, customer_name
from table1
union
select customer_id, customer_name
from table2)

, lastOrder as
(select customer_id, max(order_id) maxOrderId
 from orders
 group by customer_id)

select *
from lastOrder join customers on lastOrder.Customer_id = customers.customer_id
 join orders on order_id = maxOrderId

Upvotes: 0

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Use ROW_NUMBER() and PARTITION BY.

  • ROW_NUMBER(): it will give sequence no to your each row
  • PARTITION BY: it will group your data by given column

When you use ROW_NUMBER() and PARTITION BY both together then first partition by group your records and then row_number give then sequence no by each group, so for each group you have start sequence from 1

Help Link: Example of ROW_NUMBER() and PARTITION BY

Upvotes: 0

Related Questions