CODEWITHSUNDEEP
sqlmariadbgroupwise-maximum
s0Nic
s0Nic

Reputation: 97

SQL: Get only the last Element out of Groups

Got an SQL stmt, where got HeatingElements with their Heatingtimes and Timestamps of the last Meassure.

SELECT
    ts_diff,
    name,
    s_per_deg,
    CAST(dates.gen_date AS DATE) AS gen_date
FROM
    heat_data
RIGHT JOIN
    dates
    ON
        dates.gen_date = DATE(ts) 
WHERE name != ''
ORDER BY gen_date DESC

My Output is at the moment:

ts_diff name                    s_per_deg   gen_date
202     HeatingElement6HeatTime 33.667      16.08.2020
205     HeatingElement4HeatTime 34.167      16.08.2020
205     HeatingElement5HeatTime 34.167      16.08.2020
208     HeatingElement7HeatTime 34.667      16.08.2020
226     HeatingElement2HeatTime 37.667      16.08.2020
199     HeatingElement6HeatTime 33.167      09.08.2020
202     HeatingElement4HeatTime 33.667      09.08.2020
204     HeatingElement5HeatTime 34.000      09.08.2020
206     HeatingElement7HeatTime 34.333      09.08.2020
223     HeatingElement2HeatTime 37.167      09.08.2020
206     HeatingElement5HeatTime 34.333      07.08.2020
209     HeatingElement7HeatTime 34.833      07.08.2020
228     HeatingElement2HeatTime 38.000      07.08.2020
202     HeatingElement6HeatTime 33.667      07.08.2020
205     HeatingElement4HeatTime 34.167      07.08.2020
198     HeatingElement6HeatTime 33.000      02.08.2020
204     HeatingElement7HeatTime 34.000      02.08.2020
202     HeatingElement5HeatTime 33.667      02.08.2020
201     HeatingElement4HeatTime 33.500      02.08.2020
223     HeatingElement2HeatTime 37.167      02.08.2020
200     HeatingElement6HeatTime 33.333      26.07.2020
203     HeatingElement4HeatTime 33.833      26.07.2020
203     HeatingElement5HeatTime 33.833      26.07.2020
206     HeatingElement7HeatTime 34.333      26.07.2020
223     HeatingElement2HeatTime 37.167      26.07.2020

What im trying is to get only the last Measure of each Element, so my Result should look like this:

ts_diff name                    s_per_deg   gen_date
202     HeatingElement6HeatTime 33.667      16.08.2020
205     HeatingElement4HeatTime 34.167      16.08.2020
205     HeatingElement5HeatTime 34.167      16.08.2020
208     HeatingElement7HeatTime 34.667      16.08.2020
226     HeatingElement2HeatTime 37.667      16.08.2020

I tried to ORDER BY gen_date GROUP BY name but i always get Errors. Didnt found a Solution yet. I cant say LIMIT because on other Databases i got other (more or less Elements).

Upvotes: 0

Views: 55

Answers (1)

Søren Kongstad
Søren Kongstad

Reputation: 1440

try using analytical functions!

SELECT ts_diff
     , name
     , s_per_deg
     , gen_date
FROM (
    SELECT ts_diff
         , name
         , s_per_deg
         , CAST(dates.gen_date AS DATE) AS gen_date
         , ROW_NUMBER() OVER (PARTITION BY name ORDER BY gen_date DESC) rn

    FROM heat_data
        RIGHT JOIN dates ON dates.gen_date = DATE(ts)
    WHERE name != ''
) a
WHERE rn = 1

(Changed partition by as per comment below)

Upvotes: 1

Related Questions