EN VI

SQL query newest entry per customer?

How to SQL query newest entry per customer

I have an issue with a sql query. There is a table with data for each day which holds information for nearly every day, per customer.

Here is a simplified example data for a span of 4 days, for 3 customers.

Day	CustomerID	Counter
20240101	1001	12
20240101	2002	4
20240101	3003	75
20240102	2002	5
20240102	3003	66
20240103	1001	13
20240103	1001	14
20240103	3003	67
20240104	1001	15
20240104	2002	1
20240104	2002	2

I need a result like this. A Entry of the highest Day per Customer.

Day	CustomerID	Counter
20240103	3003	67
20240104	1001	15
20240104	2002	2

How do i achieve to get the counter of the last entry per day for each customer? Grateful for your help.

Solution:

We can use ROW_NUMBER() here:

WITH cte AS (
    SELECT Day, CustomerID, Counter,
           ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Day DESC) rn
    FROM yourTable
)

SELECT Day, CustomerID, Counter
FROM cte
WHERE rn = 1
ORDER BY Day, CustomerID;

Tags

Pinned Posts

Top 5 Best High CPM Ads Networks For Publishers In 2023

Tags: ads, Publishers, Ads Networks

HawkHost Coupon 2023 – Hawk Hosting Discount Code Up to 40% Lifetime Update Every Day

Tags: Server, host, cloud

Author

Best Solution

Bot Lv 9

Keyword

SQL query newest entry per customer, sql

Answer