Find without using lead lag and CTE missing value from a list

sql get next row value
how to use values from previous or next rows in a sql server query
how to compare current value with previous value in sql
sql: selecting rows where column value changed from previous row
how to get next row value in sql server 2008
get previous row value in mysql
sql lag
sql lead

If I am given a table like below. Now based on this we know that there is only 1 ID i.e. 4 which is missing. Is there a way to find this without using Lead, Lag, CTE and only using single select and Joins

ID  Name
1   Tom
2   Betty
3   Ram
5   John
6   Doe

You can use a self join and check if the "previous" value is missing:

SELECT t1.ID - 1 AS Missing_ID
FROM table1 t1
LEFT JOIN table1 t2 ON t2.ID = t1.ID - 1
WHERE t2.ID IS NULL AND t1.ID > 1

Output:

Missing_ID
4

SQLFiddle Demo

How to Find a Gap in a Sequence using SQL SERVER, Solve this puzzle to learn how to find a gap in a sequence using SQL Server. I was recently reviewing my bank statement and noticed they flagged the start of a missing check number with ***'s. define a partition and then use LEAD to see whether the next number was more than The CTE is used to get the next value. There is another parameter in LEAD/LAG functions and it is the offset. if not used then the default is 1. LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause ) In your case you need to find the offset of how many rows to "go back" and you can do that by using an intermediate window calculation.

If you just want to detect a single missing gap, then consider the following query:

SELECT ID - 1 AS missing
FROM yourTable t1
WHERE NOT EXISTS (SELECT 1 FROM yourTable t2 WHERE t2.ID = t1.ID - 1) AND ID > 1;

The logic here is to check, for each record, that an ID value which is one less than the current row being scanned does not exist. In this case, we return the current ID value minus one.

Demo

But a perhaps better approach would be to left join a table containing the full possible sequence to your current table, and then asserting the missing values:

WITH numbers AS (
    SELECT 1 AS Number 
    UNION ALL
    SELECT Number + 1
    FROM Numbers
    WHERE Number + 1 <= 6     -- replace with greatest number in your sequence
) 

SELECT n.Number
FROM numbers n
LEFT JOIN yourTable t
    ON n.Number = t.ID
WHERE t.ID IS NULL
OPTION (MAXRECURSION 255);

How to Use Values from Previous or Next Rows in a SQL Server Query, Before I get to the problem, here's a script you can run to set up an environment ActivityTime datetime NOT NULL Here's the data produced by the CTE with its RowNumber column: SQL Server 2012 introduced the new Lag() and Lead() functions that encapsulate There is a key component missing. I've made the following calculation involving LAG(): (lag(fValue,1,fValue) OVER (PARTITION BY Cluster ORDER BY iSequence) + fValue) / 2 as fValueAjusted. It takes the previous (based on iSequence) record's fValue, sums with current one, and divides it by 2. But, instead of using fValue, I must do that using previous record's fValueAjusted.

Try this with the ROW_NUMBER() function:

SELECT tempid
FROM
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS tempid
    FROM #temp
) a
LEFT JOIN #temp
    ON a.tempid = #temp.id
WHERE #temp.id IS NULL;

tempid
4

Demo

Joe Celko's SQL for Smarties: Advanced SQL Programming, L LAG functions, 551–552 LEAD functions, 551–552 Leaf nodes, 665 Leap year, result, 347–348 with join, avoiding, 348–349 not equality, 348 NULL values result, 168 LIST() aggregate function by crosstabs, 531–532 with recursive CTE, see Extrema functions Missing numbers in filling, 605–607 Missing table,​  During the series to keep the learning maximum and having fun, we had few puzzles. One of the puzzle was simulating LEAD() and LAG() without using SQL Server 2012 Analytic Function. Please read the puzzle here first before reading the solution : Write T-SQL Self Join Without Using LEAD and LAG.

SQL SERVER, “How to access Previous Row Value and Next Row Value in simple to get Previous and Next value with the help of Lead and Lag Function in SQL Server. However, if you are using an earlier version of SQL Server which does not support LEAD LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1 Both the LEAD and LAG function returns values that are calculated from another row in your result set to the current row. You don’t need to perform a self-join to do this. The difference is that the LEAD function looks in records that appear after the current record, and LAG looks in records that appear before the current record.

Fill in missing dates with data value from previous populated date for , Sign up or log in to view your list. Use LEAD() to get the next row within the TicketId partition. Then join to a Calendar table to get all the dates between. This is a quick way of doing (I have not tested for performance or scalablity) This is the max date from TicketAssigment group by TicketID ) select Cte. In time series data, it is generally required to calculate lag and lead of one or more measured variables. Time series or longitudinal data are considered one of the most challenging data manipulation tasks.

10 SQL tricks that you didn't think were possible, In order to understand the value of these 10 SQL tricks, it is first important Using the simple WITH clause, you can specify a list of table variables I.e. the second subquery is allowed to select from the very CTE that we're about to declare. In the case of LEAD() and LAG(), we simply access a single row  You can see the LEAD and LAG functions now have interval of rows when they are returning results. As there is interval of two rows the first two rows in LEAD function and last two rows in LAG function will return NULL value. You can easily replace this NULL Value with any other default value by passing third parameter in LEAD and LAG function.

Comments
  • I tried your solution, it gives wrong ans. Can you also explain your approach a little bit
  • Sorry, I had a typo in there. It works now, but I am also adding another approach.
  • Its working now, thank you. Can maybe write a small explanation of your solution
  • I like the recursive CTE for a numbers table. Does (MAXRECURSION 255) mean that it won't generate a value greater than 256?
  • @Nick Yes, but that number can be increased, reasonably speaking at least.