Last record of each hour of each day

carinavb · March 22, 2018, 8:42pm

Hello evrybody, I have this server
select @@version
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

And this data table:
select top 20 fecha, hora, valor from datos where id_estacion=1 and tipo_sensor=3 order by fecha desc, hora desc

2018-03-22 00:00:00.000	1900-01-01 17:30:00.000	1158,7224
2018-03-22 00:00:00.000	1900-01-01 17:15:00.000	1158,7198
2018-03-22 00:00:00.000	1900-01-01 17:00:00.000	1158,7173
2018-03-22 00:00:00.000	1900-01-01 16:45:00.000	1158,7147
2018-03-22 00:00:00.000	1900-01-01 16:30:00.000	1158,7147
2018-03-22 00:00:00.000	1900-01-01 16:15:00.000	1158,7122
2018-03-22 00:00:00.000	1900-01-01 16:00:00.000	1158,7096
2018-03-22 00:00:00.000	1900-01-01 15:45:00.000	1158,7096
2018-03-22 00:00:00.000	1900-01-01 15:30:00.000	1158,7071
2018-03-22 00:00:00.000	1900-01-01 15:00:00.000	1158,702
2018-03-22 00:00:00.000	1900-01-01 14:45:00.000	1158,6994
2018-03-22 00:00:00.000	1900-01-01 14:30:00.000	1158,6969
2018-03-22 00:00:00.000	1900-01-01 14:00:00.000	1158,6943
2018-03-22 00:00:00.000	1900-01-01 13:45:00.000	1158,6918
2018-03-22 00:00:00.000	1900-01-01 13:30:00.000	1158,6892
2018-03-22 00:00:00.000	1900-01-01 13:15:00.000	1158,6867
2018-03-22 00:00:00.000	1900-01-01 12:30:00.000	1158,6816
2018-03-22 00:00:00.000	1900-01-01 12:15:00.000	1158,679
2018-03-22 00:00:00.000	1900-01-01 12:00:00.000	1158,679
2018-03-22 00:00:00.000	1900-01-01 11:45:00.000	1158,6739

I need the last record of each hour of each day, for example, the result must be..
|2018-03-22 00:00:00.000|1900-01-01 17:30:00.000|1158,7224|
|2018-03-22 00:00:00.000|1900-01-01 16:45:00.000|1158,7147|
|2018-03-22 00:00:00.000|1900-01-01 15:45:00.000|1158,7096|
|2018-03-22 00:00:00.000|1900-01-01 14:45:00.000|1158,6994|
|2018-03-22 00:00:00.000|1900-01-01 13:45:00.000|1158,6918|
|2018-03-22 00:00:00.000|1900-01-01 12:30:00.000|1158,6816|
|2018-03-22 00:00:00.000|1900-01-01 11:45:00.000|1158,6739|

How should I do this?
Someone can help me?
Best Regards
Carina

bitsmed · March 22, 2018, 10:38pm

I must urge you to consider upgrading to newer version, as you current version is deprecated.

On current version I would do:

Query

select top(1)
       fecha
      ,hora
      ,valor
  from datos
 where id_estacion=1
   and tipo_sensor=3
 order by row_number() over(partition by fecha
                                        ,datediff(hour,0,hora)
                                order by hora desc
                           )
;

On your version i think this would work:

Query

select b.fecha
      ,b.hora
      ,b.valor
  from (select fecha
              ,max(hora) as hora
          from datos
         where id_estacion=1
           and tipo_sensor=3
         group by fecha
                 ,datediff(hour,0,hora)
       ) as a
       inner join datos as b
               on b.id_estacion=1
              and b.tipo_sensor=3
              and b.fecha=a.fecha
              and b.hora=a.hora
;

jotorre_riversidedpss.org · March 22, 2018, 10:40pm

[code]
WITH t
AS(
SELECT
Cast('20180322' AS date) fecha
, Cast('17:30' AS time) hora
, '1158,7224' valor UNION ALL
SELECT '20180322', '17:15', '1158,7198' UNION ALL
SELECT '20180322', '17:00', '1158,7173' UNION ALL
SELECT '20180322', '16:45', '1158,7147' UNION ALL
SELECT '20180322', '16:30', '1158,7147' UNION ALL
SELECT '20180322', '16:15', '1158,7122' UNION ALL
SELECT '20180322', '16:00', '1158,7096' UNION ALL
SELECT '20180322', '15:45', '1158,7096' UNION ALL
SELECT '20180322', '15:30', '1158,7071' UNION ALL
SELECT '20180322', '15:00', '1158,702' UNION ALL
SELECT '20180322', '14:45', '1158,6994' UNION ALL
SELECT '20180322', '14:30', '1158,6969' UNION ALL
SELECT '20180322', '14:00', '1158,6943' UNION ALL
SELECT '20180322', '13:45', '1158,6918' UNION ALL
SELECT '20180322', '13:30', '1158,6892' UNION ALL
SELECT '20180322', '13:15', '1158,6867' UNION ALL
SELECT '20180322', '12:30', '1158,6816' UNION ALL
SELECT '20180322', '12:15', '1158,679' UNION ALL
SELECT '20180322', '12:00', '1158,679' UNION ALL
SELECT '20180322', '11:45', '1158,6739'
),
T2 AS(SELECT t.fecha
, t.hora
, Row_Number() OVER(PARTITION BY DatePart(HH, t.hora) ORDER BY t.fecha DESC, t.hora DESC) rn
FROM t)
SELECT t.fecha
, t.hora
, t.valor
FROM t
JOIN T2 ON t.fecha = T2.fecha AND t.hora = T2.hora
WHERE T2.rn=1;

[code]

Pasi · March 22, 2018, 10:43pm

Assuming 'hora' is your hour, since all your columns are in Spanish I guess, try below, see if it works?

select fecha, hora, valor
from datos t1
inner join (select fecha, max(hora) as Maxhora
from datos
group by hora) t2
on t1.fecha =t2.fecha
and t1.hora=t2.Maxhora
where id_estacion=1
and tipo_sensor=3

carinavb · March 23, 2018, 6:32pm

HI bitsmed thanks for the answer. I can't update the server. It's a hosting. And the query doesn't work either....
I get this error..
'row_number' is not a recognized function name.

Best regards
Carina

carinavb · March 23, 2018, 6:34pm

Hi Pasi, thanks for the answer, I get this data

|2018-03-23 00:00:00.000|1900-01-01 15:00:00.000|1158,8983|
|2018-03-23 00:00:00.000|1900-01-01 14:45:00.000|1158,8958|
|2018-03-23 00:00:00.000|1900-01-01 14:30:00.000|1158,8932|
|2018-03-23 00:00:00.000|1900-01-01 14:15:00.000|1158,8907|
|2018-03-23 00:00:00.000|1900-01-01 14:00:00.000|1158,8907|
|2018-03-23 00:00:00.000|1900-01-01 13:45:00.000|1158,8856|
|2018-03-23 00:00:00.000|1900-01-01 13:30:00.000|1158,8856|
|2018-03-23 00:00:00.000|1900-01-01 13:15:00.000|1158,883|
|2018-03-23 00:00:00.000|1900-01-01 13:00:00.000|1158,8805|
|2018-03-23 00:00:00.000|1900-01-01 12:45:00.000|1158,8779|

Regards..

carinavb · March 23, 2018, 6:36pm

Thanks for the answer but I get this error,
'Row_Number' is not a recognized function name.

The server is old ...
Regards

Pasi · March 23, 2018, 6:57pm

So is it working or not? couldn't tell with your reply?
Pasi.

carinavb · March 23, 2018, 6:59pm

It's not working I get the error
'Row_Number' is not a recognized function name.

Carina

Pasi · March 23, 2018, 7:02pm

Hello Carinavb, you said below, you are saying you are gtting row_number error? I am confused?

yosiasz · March 23, 2018, 7:06pm

and is it Microsoft SQL Server or is it MySql database

carinavb · March 23, 2018, 7:06pm

I'm sorry...the data retrieved is wrong...
It should retrieve only one data per hour and with the query I get this...

|2018-03-23 00:00:00.000|1900-01-01 15:00:00.000|1158,8983|
|2018-03-23 00:00:00.000|1900-01-01 14:45:00.000|1158,8958|
|2018-03-23 00:00:00.000|1900-01-01 14:30:00.000|1158,8932|
|2018-03-23 00:00:00.000|1900-01-01 14:15:00.000|1158,8907|
|2018-03-23 00:00:00.000|1900-01-01 14:00:00.000|1158,8907|
|2018-03-23 00:00:00.000|1900-01-01 13:45:00.000|1158,8856|
|2018-03-23 00:00:00.000|1900-01-01 13:30:00.000|1158,8856|
|2018-03-23 00:00:00.000|1900-01-01 13:15:00.000|1158,883|
|2018-03-23 00:00:00.000|1900-01-01 13:00:00.000|1158,8805|
|2018-03-23 00:00:00.000|1900-01-01 12:45:00.000|1158,8779|

bitsmed · March 23, 2018, 7:13pm

As I wrote, the first query works for newer version than yours.
The second one should work for you version. Did you try it?

carinavb · March 23, 2018, 8:42pm

You're right that worked!!!!!!

|2018-03-23 00:00:00.000|1900-01-01 17:15:00.000|1158,9136|
|2018-03-23 00:00:00.000|1900-01-01 16:45:00.000|1158,9085|
|2018-03-23 00:00:00.000|1900-01-01 15:45:00.000|1158,9009|
|2018-03-23 00:00:00.000|1900-01-01 14:45:00.000|1158,8958|
|2018-03-23 00:00:00.000|1900-01-01 13:45:00.000|1158,8856|
|2018-03-23 00:00:00.000|1900-01-01 12:45:00.000|1158,8779|
|2018-03-23 00:00:00.000|1900-01-01 11:15:00.000|1158,8652|
|2018-03-23 00:00:00.000|1900-01-01 10:45:00.000|1158,8601|
|2018-03-23 00:00:00.000|1900-01-01 09:45:00.000|1158,855|
|2018-03-23 00:00:00.000|1900-01-01 08:45:00.000|1158,8448|

Thanks a lot!!!!!