MySQL, Query to filter out results that appear with same time status

Question

    id          | smartMeterId    | unitConsumed | time
    ==========================================================
    .....747710c|....-aa92a7f4e66c|  1.55        | 2024-01-29 19:30:01
   -05f2042af6c3|...b-aa92a7f4e66c|  1.55        | 2024-01-29 19:30:01
    .....747710c|....-aa92a7f4e66c|  1.55        | 2024-01-29 19:30:01
   -05f2042af6c3|...b-aa92a7f4e66c|  1.02        | 2024-01-29 22:45:01
    .....747710c|....-aa92a7f4e66c|  1.02        | 2024-01-29 22:45:01
   -05f2042af6c3|...b-aa92a7f4e66c|  1.52        | 2024-01-30 15:35:01

While trying to calculate unitsConsumed for each meter, some inputs appear twice, it's quite obvious since they have same time window, i.e on the column time, they have the same timestamp. How do i filter out the multiples of these logs with the same timestamp.

WITH RankedRows AS (
  SELECT
    id,
    smartMeterId,
    unitConsumed,
    time,
    ROW_NUMBER() OVER (PARTITION BY smartMeterId, time ORDER BY time) AS row_num
  FROM
    demodb.SmartMeterUsageHistory
)
SELECT
  id,
  smartMeterId,
  unitConsumed,
  time
FROM
  RankedRows
WHERE
  row_num = 1 AND smartMeterId = 'sss';

i wanted to group same time status in columns and return just one for each multiple columns it grouped.

Answer 1

You can add a unique constraint for the combo of time and Id(assuming it's distinct for every meter). Try something like this

WITH RankedRows AS (
  SELECT
    id,
    smartMeterId,
    unitConsumed,
    time,
    ROW_NUMBER() OVER (PARTITION BY smartMeterId, time ORDER BY time) AS row_num
  FROM
    demodb.SmartMeterUsageHistory
)
SELECT DISTINCT ON (smartMeterId, time)
  id,
  smartMeterId,
  unitConsumed,
  time
FROM
  RankedRows
WHERE
  smartMeterId = 'sss'
ORDER BY
  smartMeterId,
  time;