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) 
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
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
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)