Get only last row in each day's multiple entries

1

I'm strugling with sorting out the last value and status for the day, given by the regtime, grouped by the MeteringPointID, register and endtime.

My SQL is running in Google BigTable and is as follows:

SELECT distinct t1.MeteringPointID, t1.register, t1.endtime, value, registrationTime as regtime
FROM MyTable t1
  inner join ( select MeteringPointID, register, endtime, max(registrationTime) as regtime
                from  MyTable
                group by MeteringPointID, register, endtime) as t2
                on t1.MeteringPointID= t2.MeteringPointIDand t1.register=t2.register and t1.endtime = t2.endtime

WHERE 1=1
and datetime(t1.endTime, 'Europe/Oslo') = datetime('2022-10-04 01:00:00') - interval '1' hour 
and t1.MeteringPointID= '707057500020667161'
order by t1.MeteringPointID, t1.register, t1.endtime

The result is given as:

image
image1282×344 44.9 KB

That means, I need to end up with only two rows

2

This should work:

WITH AddRowNumbers AS (
SELECT t1.MeteringPointID, t1.register, t1.endtime, t1.value, t1.registrationTime as regtime
,ROW_NUMBER() OVER (PARTITION BY t1.MeteringPointID, t1.register, t1.endtime ORDER BY t1.registrationTime DESC) as row_num
FROM MyTable t1
WHERE datetime(t1.endTime, 'Europe/Oslo') = datetime('2022-10-04 01:00:00') - interval '1' hour 
AND t1.MeteringPointID= '707057500020667161')
SELECT MeteringPointID, register, endtime, value, regtime
FROM AddRowNumbers
WHERE row_num=1
ORDER BY MeteringPointID, register, endtime

Note that SQLTeam.com is a Microsoft SQL Server focused site, we're not experts on Google BigTable.

In any case, the ROW_NUMBER() function will give you what you need, and the WHERE clause outside the Common Table Expression filters to rows that are numbered 1.

More info here:

1 Like
3

Works like a dream. Real magic @robert_volk. Thanks!

4

@robert_volk
With your help I got the result

image
image1362×178 20.2 KB

... But is it possible to combine the epression using with AddRowsNumbers with a pivot function? Then I want to get only one row as a result for the select MeteringPointId, EndTime, and pivotet register and value.
If I run a pivot without sorting out old entries I get the following:

with pivot_data as(
  SELECT distinct meteringPointId, endtime, registrationTime, register, value, status--, validationcode as val, estimationcode as est
  FROM MyTable
  WHERE 1=1
 -- and datetime(endTime, 'Europe/Oslo') > datetime('2022-10-04 01:00:00') - interval '1' hour --korrigere for MDMx døgn (lokal tid)
  and datetime(endTime, 'Europe/Oslo') = datetime('2022-10-04 04:00:00') - interval '0' hour --sjekker spesifikk time i intervallet
  and meteringPointID = '707057500020667161'
  )
  select * from pivot_data
  pivot (sum(value) for register in ('AP', 'AM'))
order by meteringpointid, endtime;
5

I'm not a BigTable expert, not even a novice, so I'm not exactly sure how to get you that result.

I did find that BigTable supports the QUALIFY operator:

That can simplify your query, you won't need the CTE to generate row numbers.

It seems the PIVOT operator supports multiple aggregations:

Scroll down to the example just before UNPIVOT.

If that doesn't work, you may be able to simply nest a (SELECT max(value) FROM pivot_data) for each of the columns you want to pivot. Other options include SELECT AS STRUCT:

6

Thanks @robert_volk !
I ended up with using inner and outer WITH statement, for combining your AddRow, with the pivot-function. Guess it could be done more neat, but it works :slight_smile:

with pivot_data as(
WITH AddRowNumbers AS (
SELECT MeteringPointID, register, endtime, value, status, estimationcode as estcode, registrationTime
,ROW_NUMBER() OVER (PARTITION BY MeteringPointID, register, endtime ORDER BY registrationTime DESC) as row_num
FROM MyTable
WHERE 1=1
and datetime(endTime, 'Europe/Oslo') > datetime('2022-10-04 01:00:00') - interval '1' hour  and datetime(endTime, 'Europe/Oslo') < datetime('2022-10-06 00:00:00') + interval '1' hour
AND MeteringPointID= '707057500020667161'
)
SELECT MeteringPointID, register, extract(date from datetime(endtime - interval '1' hour, 'Europe/Oslo')) as dato, sum(value) as value
FROM AddRowNumbers
WHERE row_num=1
GROUP BY MeteringPointID, register, dato
ORDER BY MeteringPointID, register, dato
)
  select * from pivot_data
  pivot (sum(value) for register in ('AP', 'AM'))
order by meteringpointid
;

Resulting in one sum pr register pr day:
image