How do I select the latest records for specific entries?

Asked

Viewed 73 times

-1

I have a table with customer purchase records, I would like to select only the last purchase of each customer. How to do this? I tried to use DISTINCT, but I have other data I need and are not possible to group, as the date.

For example:

Client A

Compra 1
Compra 2

Client B

Compra 1
Compra 2

Client C

Compra 1
Compra 2
Compra 3

The Result must be:

Cliente A Compra 2
Cliente B Compra 2
Cliente C Compra 3
sql-server

  • you can make a sub-query to bring that information, or left

  • Fernando, could you pass more information on the table? tell what is the type of data stored in the com column Compra 1, this table has some column of id? this information is important for a solution more suitable to your problem.

3 answers

1

You can partition to query using the 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

  • I didn’t quite understand this function, but I managed to apply and solve my problem. I will read more about and try to understand better, thank you.

0

tried to use MAX in the Purchase field, grouping the customer ? see the example below if it serves you. NOTE: I am considering the PURCHASE field as a intso 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

0

Recital: create table Purchase (Idcompra int, Customer varchar(1))

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
)

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.