SQLTeam.com | Weblogs | Forums

Latest value for a collection on counters

Hello,
I have a set of vending machines and each one has a set of counters for each product. The counters increment for each vend and are read at certain intervals. So, if I read a counter and I want the quantity sold in that interval of time I need to find the last value of the counter, prior to the current one.
Counter readings are stored in a table with the moment of the reading, Counter Id and counter value.
Let's say, for only one vending machine, I have counters C1, C2, C3 and C4 and the following readings:
Readings:
+----------------+----+----+
| Moment | Id |Val |
+----------------+----+----+
| 2019-05-30 | C1 | 10 |
| 2019-05-30 | C3 | 5 |
| 2019-05-31 | C2 | 7 |
| 2019-05-31 | C3 | 8 |
| 2019-06-02 | C1 | 12 |
| 2019-06-02 | C4 | 5 |
+----------------+----+----+
The values I am looking for are: C1=12; C2=7; C3=8; C4=5

Can you sugest a Select query that could perform this?

Thank you,
Daniel

If the Moment data has only date and no time, and if there are two readings for the same Id on the same day, what should happen? If there is no time portion, unless you have some other columns or some other logic, you wouldn't be able to tell which one came first and which one came last.

;WITH cte AS
    (
    	SELECT 
    		*,
    		N = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Moment DESC)
    )
    SELECT 
    	Id, Val 
    FROM
    	cte
    WHERE
    	N = 1;
2 Likes

Hello James and thank you for the answer.
Moment is a datetime but I made it simpler for the example.
I have tried it and it works! At this point I am still trying to figure out how (I haven't use WITH before) :slight_smile:

Thank you again,
Daniel

The with construct as used here is generally referred to as a CTE (Common Table Expression). There is lot of documentation and examples from Microsoft as well as other sources. CTE's have a number of features and benefits, but in this case, it is equivalent to a subquery, as shown below.

SELECT 
    Id, Val 
FROM
    (
    	SELECT 
    		*,
    		N = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Moment DESC)
		FROM
			YourTable
	) AS S
WHERE
    N = 1;
1 Like

hi

i know this post is solved

but what about ?? this sql ..seems simple

drop create data ..
declare @temp table  
(
moment date,
id varchar(2),
Val int
)
insert into @temp select '2019-05-30','C1',  10 
insert into @temp select '2019-05-30','C3',  5 
insert into @temp select '2019-05-31','C2',  7 
insert into @temp select '2019-05-31','C3',  8 
insert into @temp select '2019-06-02','C1',  12 
insert into @temp select '2019-06-02','C4',  5 
select * from @temp
SQL...
select id,max(val) from @temp
group  by id

image

Hello,
Thank you for answering but this doesn't work.
It's not about getting the Maximum values but the latest ones (i.e. related to Moment not to Val).
The Val field can have lower values today than yesterday.

Thanks again,
Daniel

hi

i am suggesting this solution if you are looking for performance

drop create data ....
declare @temp table  
(
moment date,
id varchar(2),
Val int
)
insert into @temp select '2019-05-30','C1',  10 
insert into @temp select '2019-05-30','C3',  5 
insert into @temp select '2019-05-31','C2',  7 
insert into @temp select '2019-05-31','C3',  8 
insert into @temp select '2019-06-02','C1',  12 
insert into @temp select '2019-06-02','C4',  5 
select * from @temp
SELECT * 
FROM   @temp D 
WHERE  moment = (SELECT Max(moment) 
                 FROM   @temp 
                 WHERE  id = D.id)

hi

another way to do this
if it helps :slight_smile: :slight_smile:

drop create data ...
declare @temp table  
(
moment date,
id varchar(2),
Val int
)
insert into @temp select '2019-05-30','C1',  10 
insert into @temp select '2019-05-30','C3',  5 
insert into @temp select '2019-05-31','C2',  7 
insert into @temp select '2019-05-31','C3',  8 
insert into @temp select '2019-06-02','C1',  12 
insert into @temp select '2019-06-02','C4',  5 
select * from @temp

using top 1 with ties

SELECT TOP 1 WITH ties * 
FROM   @temp 
ORDER  BY Row_number() 
            OVER ( 
              partition BY id 
              ORDER BY moment DESC)