Calling Teayesqyell Experts.. Plz Help

[Quickgun_murugan]

idi edo chinna issue ney ayuntadi but naaku osthaledu... help plz...

 

 

 create table events (

      sensor_id integer not null,

      event_type integer not null,

      value integer not null,

      time datetime unique not null

  );

sensor_id  | event_type | value      | time

  ------------------------------------------------

  2          | 2          | 5          | 2014-02-13 12:42:00

  2          | 4          | -42        | 2014-02-13 13:19:57

  2          | 2          | 2          | 2014-02-13 14:48:30

  3          | 2          | 7          | 2014-02-13 12:54:39

  2          | 3          | 54         | 2014-02-13 13:32:36

 

 

 

  insert into events values (2, 2, 5, '2014-02-13 12:42:00')

 

  insert into events values (2, 4, -42, '2014-02-13 13:19:57')

insert into events values (2, 2, 2, '2014-02-13 14:48:30')

insert into events values (3, 2, 7, '2014-02-13 12:54:39')

insert into events values (2, 3, 54, '2014-02-13 13:32:36')

 

select * from events

 

result idi ravali

 

 

  sensor_id  | event_type | value

  ----------------------------

  2          | 2          | 2

  2          | 3          | 54

  2          | 4          | -42

  3          | 2          | 7

 

 

nenu ee code rasa but edo miss avutundi

 

SELECT distinct a.sensor_id, a.event_type, b.value

from events as a

inner join 

(Select max(time) as time, value from events 

group by value)as b

on a.time = b.time and a.value = b.value

where 

order by sensor_id, event_type

 

result idi ostundi

 

  sensor_id  | event_type | value

  -------------------------------

  2          | 2          | 2

  2          | 2          | 5      ------->>>>  its not recent for the sensor_id and event_type combination 

  2          | 3          | 54

  2          | 4          | -42

  3          | 2          | 7

 

logic n correct query ento cheppi punyam kattukondi bhayyaas..

[Quickgun_murugan]

ltt

[Renault]

order by datetime field sesthe aipoddi ga  

 

select * from events  

order by datetime, sensor_id  asc, event_type asc

[WeBeliveInTigerman]

WITH t AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY sensor_id, event_type ORDER BY time DESC) AS recent  
FROM [Table]
)
SELECT * FROM t WHERE recent = 1

[JollyBoy]

with sensor as ( select a.sensor_id, a.event_type,a.value,

ROW_NUMBER() OVER (Partition by  a.sensor_id, a.event_type ORDER BY a.time desc) AS RN

from events a ) 

select * from sensor  where RN = 1

[JollyBoy]

WITH t AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY sensor_id, event_type ORDER BY time DESC) AS recent  
FROM [Table]
)
SELECT * FROM t WHERE recent = 1

+1 type chese lopala esinav 

[JollyBoy]

WITH t AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY sensor_id, event_type ORDER BY time DESC) AS recent  
FROM [Table]
)
SELECT * FROM t WHERE recent = 1

+1 type chese lopala esinav 

[arshad]

WITH t AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY sensor_id, event_type ORDER BY time DESC) AS recent  
FROM [Table]
)
SELECT * FROM t WHERE recent = 1

+1

[ITeachSQLServer]

;WITH cte AS
(
 SELECT    ROW_NUMBER() OVER (partition BY sensor_id,event_type ORDER BY sensor_id) AS RowNumber, sensor_id,event_type,value, time  FROM events
)

SELECT * FROM cte  where RowNumber =1

[Quickgun_murugan]

Thanks all... Ranking function vadatam miss ayya... gotcha...