Stack Overflow
Stack Overflow

Questions tagged [lead]

Accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2017. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

For further reading https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql

309 questions
1
vote
0 answers

How to find the longest consecutive streak in a tibble column by using the lag() or lead() functions

I have the following tibble: my_tbl <- tribble( ~year, ~event_id, ~winner_id, 2011, "A", 4322, 2011, "A", 9604, 2011, "A", 1180, 2013, "A", 4322, 2013, "A", 9604, 2013, "A", 1663, …
lmcjfal
  • 85
  • 5
1
vote
2 answers

Delete group based if there are 3 consecutive identical values and if the leading value is less than the previous

I just need to delete, by group, rows that contain the same value 3 times in a row and leading rows that are less than previous rows. I've tried dplyr's lead function, but have been unsuccessful. The example data are just a subset of thousands of…
TheSciGuy
  • 1,154
  • 11
  • 22
1
vote
0 answers

Facebook API can't retrieve leads. Get error code 100 and error subcode 33

I have an application that makes 1 daily query to the API of leads to extract the information. Following what the documentation, I send a GET request as…
Dvex
  • 921
  • 2
  • 11
  • 35
1
vote
1 answer

Oracle query to find the least change before a new change in a given data set

I am trying to do an update on a table using its own data, and i am struggling to come up with a query for my requirement please see the data below KEY VALID_DATE STATUS INACTIVE_DT 1 7/2/2019 ACTIVE 1 7/27/2018 INACTIVE 1 …
user1751356
  • 565
  • 4
  • 14
  • 33
1
vote
2 answers

How to get previous or next row in a given row in mysql using lead or lag?

I've been reading on lead and lag functions but I still don't get it. My sql syntax is as follows: SELECT description, lag(description) over (ORDER BY transid ASC) FROM transaction WHERE transid = 20; It's not returning the desired data. I…
buencamino
  • 91
  • 1
  • 10
1
vote
2 answers

Find next record where status field is different from current

I have a table that is used to log events. Two types specifically : ON and OFF. There are sometimes overlapping log entries as there can be 2 simultaneous devices logging. This is not crucial, as the end report should give a [mostly] correct…
callisto
  • 4,921
  • 11
  • 51
  • 92
1
vote
2 answers

Check for consecutive values and then make a decision

I have a dataset where i need to check for next 7 consecutive dates if value is 0 then we will put all the in between value as 0 if the values in between are not 0 for 7 days then we will mark all value as 1. eg: Dataset Days Values …
Nit2702
  • 19
  • 4
1
vote
1 answer

Using lead with dplyr to compute the difference between two time stamps

I want to find the difference between two time stamps based on finding the time stamp in one column based on a condition, "Start", and then finding the time stamp for the first row that meets another condition in that same column, "Stop". Basically…
Meghan Martin
  • 15
  • 2
1
vote
1 answer

pyspark lag function on one column based on the value in another column

I want to be able to create a lag value based on the value in one of the columns. in the data given Qdf is the Question dataframe and Adf the Answer dataframe. I have given an additional explanation column (which I actually dont need in my final…
SatZ
  • 430
  • 5
  • 14
1
vote
0 answers

Using dplyr's lead and lag with a range

I am trying to create a function to calculate the mean of the last n rows in a data.frame for each row using tidyverse's syntax. So, the way I see it is using lead but dynamically with an n value. Something like df %>% mutate(mean_5 =…
Bernardo
  • 461
  • 7
  • 20
1
vote
1 answer

window functions( lag, lead) implementation in pyspark?

Below is the T-SQL code attached. I tried to convert it to pyspark using window functions which is also attached. case when eventaction = 'IN' and lead(eventaction,1) over (PARTITION BY barcode order by barcode,eventdate,transactionid)…
Katelyn Raphael
  • 253
  • 2
  • 4
  • 16
1
vote
1 answer

Is anything like LAG() or LEAD() in SQL Server 2000

I want to get the next row value in current row. Just like LEAD() does in latest SQL Server. But I am currently using SQL Server 2000. And I don't have any other option. Actual scenario is: TableRule: # HeadNo | NextHeadNo | …
Irfan
  • 665
  • 6
  • 29
1
vote
2 answers

MS SQL SERVER LAG

I'm trying to apply a condition to LAG in a SQL query. Does anyone know how to do this? This is the query: SELECT CONCAT([FirstName],' ',[LastName]) AS employee, CAST([ArrivalTime] AS DATE) AS date, …
Leeann Urban
  • 25
  • 6
1
vote
1 answer

T-SQL Issue using lead or lag

I have a table that has columns EVENT_ACTION and TIMESTAMP; in column EVENT_ACTION there are two possible values, 225 and 226. 225 represent the start_time and 226 represent the end_time; since they are in two different rows I'm trying to use LAG or…
Mike
  • 1,221
  • 3
  • 15
  • 34
1
vote
1 answer

Oracle SQL - Returning multiple row results into one single row using LAG or LEAD

I'm trying to figure out how to return 1 row for multiple row results. Currently my code looks like the following: select x.Reference, x.date "Date1", x.char "Char1", lead(x.date, 1) OVER (PARTITION BY x.Reference ORDER BY x.date) as…
MackScania
  • 23
  • 3
1 2 3
…
20 21