I have a table with customer purchase records, I would like to select only the last purchase from each customer. How to do this? I tried to use DISTINCT, but I have other data I need and it is not possible to group such as date.
For example:
Customer A
Compra 1
Compra 2
Client B
Compra 1
Compra 2
Client C
Compra 1
Compra 2
Compra 3
The result should be:
Cliente A Compra 2
Cliente B Compra 2
Cliente C Compra 3
You can partition to query using window function ROW_NUMBER .
WITH CTE_Compras AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY ClienteId ORDER BY CompraId DESC) AS Ordem
ClienteId,
CompraId
FROM Compras
)
SELECT ClienteId, CompraId FROM CTE_Compras WHERE Ordem = 1
Have you tried using MAX in the Purchase field, grouping the customer? see the example below if it suits you. Note: I'm considering the PURCHASE field as a int so it's working.
declare @Compras as Table (Cliente varchar(1), Compra int)
declare @Cliente int
insert into @Compras (Cliente, Compra)
Values ('A',1),('A',2),('A',3),('B',1),('B',2),('C',1),('C',2),('C',3),('C',4)
-- ====================================
-- Exibindo todas as compras
-- ====================================
SELECT Cliente, Compra
FROM @Compras
-- ====================================
-- Exibindo o ultimo registro de compra
-- ====================================
SELECT Cliente , MAX(Compra) as Compra
FROM @Compras
GRoup by Cliente
Considering: create table Purchase (IDCompra int, varchar (1) Client)
insert into Compra (IDCompra, CLiente)
select 1, 'A'
union select 2, 'A'
union select 1 ,'B'
union select 2 ,'B'
union select 3 ,'B'
union select 1 ,'C'
union select 2 ,'C'
union select 3 ,'C'
union select 4 ,'C'
select
*
from
Compra c
where
Exists
(
select max(IDCompra) from Compra cAux
where c.Cliente = cAux.Cliente
group by Cliente
having max(IDCompra) = c.IDCompra
)