How to get data from the previous line in SQL?

Asked

Viewed 2,443 times

3

How to create a column with the value of the previous row of another column? Follow example, I have the column time and I want to create the column time_lag:

Tabela inicial:  
id | title   | time  | domain  
32   title1    12:30   domain1  
33   title1    13:30   domain2  
34   title2    14:20   domain1  
35   title2    14:50   domain2  
36   title3    15:30   domain5

Expected result: 

id | title   | time  | domain |time_lag  
32   title1    12:30   domain1  
33   title1    13:30   domain2 12:30  
34   title2    14:20   domain1 13:30  
35   title2    14:50   domain2 14:20  
36   title3    15:30   domain5 14:50
sql

2 answers

2

You can create a new column to your table and at the time of inserting do something like:

INSERT INTO minhatabela (title, time, domain, time_lag) VALUES 
(
    'title1',
    '12:30', /*Não sei o tipo deste campo apesar do nome*/
    'domain1',
     SELECT time FROM minhatabela ORDER BY id DESC LIMIT 1
)

This would make the time_lag value the time value of the last entered record

1


If your id is always auto increment, just do a sub-select and pass the id - 1 of the external select.

declare @teste table
(
   id int,
   title varchar(10),
   time time, 
   domain varchar(10)
)


insert into @teste values

(32,   'title1'   ,'12:30'   ,'domain1'),  
(33 ,  'title1'    ,'13:30'   ,'domain2'),    
(34,   'title2'    ,'14:20'   ,'domain1'),    
(35,   'title2'    ,'14:50'   ,'domain2'),    
(36 ,  'title3'    ,'15:30'   ,'domain5')  


select * , (select time from @teste t1 where t1.id = t2.id - 1) as time_lag
from @teste t2

inserir a descrição da imagem aqui

Or else;

select * , (select top 1 time from @teste t1 where t1.id < t2.id order by time desc ) as time_lag
from @teste t2

Exchange top 1 for rownum = 1 if oracle or LIMIT 1 if mysql.

  • From the standpoint of normalization it would be better to use this approach (different from the one asked by the author) to make a select even, as you did. Since the result of the time_lag column is a calculated value. But you should note that removing any record from the table would break the logic of -1

  • @Felipenascimento, truth, solved.

Browser other questions tagged sql

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