Select picking up previous line

Asked

Viewed 838 times

0

What I have to use in SQL SERVER 2008 to get the line before a certain enclosure. For example if in my Where below if the alert is = to 99 want to know the alert and the speed before the alert.

Speed     Data                  Alerta
58   '2017-09-13 10:08:04.290'  44
20   '2017-09-13 10:08:51.340'  99
56   '2017-09-13 10:09:21.450'  204
40   '2017-09-13 10:09:27.470'  99
34   '2017-09-13 10:09:37.570'  204

In this example there were two alert 99 where he was at a speed of 20 and 40 km, I want to return this previous line to the alert with the highest speed:

Speed     Data                  Alerta
56   '2017-09-13 10:09:21.450'  204
sql sql-server query

  • Wouldn’t be the 58 '2017-09-13 10:08:04.290' 44 higher speed ?

  • Possible duplicate of How to use LEAD and LAG using a condition

  • @Marcelomartins: Does the table have a column that uniquely identifies each row? That is, does it have a primary key? // And if there is more than one alert 99, but at the same speed?

2 answers

2


A little big, but it solves your problem..

declare @Velocidades table
(
    Speed int,
    Data Datetime,
    Alerta int
);


insert into @Velocidades  values
(58,'2017-09-13 10:08:04.290',44),
(20,'2017-09-13 10:08:51.340',99),
(56,'2017-09-13 10:09:21.450',204),
(40,'2017-09-13 10:09:27.470',99),
(34,'2017-09-13 10:09:37.570',204)

declare @VelocidadesTeste table
(
    Speed int,
    Data Datetime,
    Alerta int
);


DECLARE @Speed int, @Data Datetime, @Alerta int;  

DECLARE db_cursor CURSOR FOR  
    SELECT Speed, Data, Alerta
    FROM @Velocidades
    WHERE Alerta = 99; 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @Speed, @Data, @Alerta   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       insert into @VelocidadesTeste
       select top 1 * from @Velocidades 
       where Data < @Data 
       order by Data desc;

       FETCH NEXT FROM db_cursor INTO @Speed, @Data, @Alerta   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor


select * from @VelocidadesTeste
where Speed = (select max(Speed) from @VelocidadesTeste)

-1

Here is a suggestion for the 2008 version of SQL Server:

-- código #1
with 
Alerta99 as (
SELECT Data, 
       Seq= row_number() over (order by Speed desc)
  from tbAlerta
  where alerta = 99
),
MaiorVelocidade99 as (
SELECT Data
  from Alerta99
  where Seq = 1
)
SELECT top (1) Speed, Data, Alerta
  from tbAlerta as A
  where A.Data < (SELECT Data from MaiorVelocidade99)
  order by A.Data desc;

CTE Alert99 separates all lines whose alert is 99 and creates column with sequence of them, starting with the highest speed and increasing as the speed decreases.

The CTE Maiorvelocity99 gets the date from the highest speed line, to alert 99.

At the end is returned the line immediately preceding the line of highest speed for alert 99, using the date as criterion.

Untested; may contain errors.

Browser other questions tagged sql sql-server query

You are not signed in. Login or sign up in order to post.