CODEWITHSUNDEEP
sqlsql-server
Riley Hun
Riley Hun

Reputation: 2785

SQL Server: Extract the rows that come after a row containing specific text

Is there a way to extract ONLY the first row that come directly after the rows containing type = 'Email'?

Sample table:

id     type      details
1   Email   [email protected]
2   1234    1234
3   Email   [email protected]
4   12345   12345
5   123456  123456
6   Email   [email protected]
7   1234567     1234567
8   12345678    12345678
9   123456789   123456789
10  Email   [email protected]
11  01      01
12  Email   [email protected]
13  012     012
14  Email   [email protected]
15  0123    0123

With Python and Pandas, I would write something like this...

indexes = table[table['type']=='Email'].index + 1
table = table.ix[indexes]

Where the output would be...

2   1234    1234
4   12345   12345
7   1234567     1234567
11  01  01
13  012     012
15  0123    0123

Upvotes: 3

Views: 129

Answers (4)

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

select  *

from   (select  *
               ,lag (type) over (order by id) as prev_type

        from    t
        ) t

where   prev_type = 'Email'

Upvotes: 3

Shruti
Shruti

Reputation: 190

Here's a solution that should work even if id has gaps. It uses window functions.

---Sample data
WITH data([id], [type] , [details]) AS
(
    SELECT 1,'Email','[email protected]' UNION ALL
    SELECT 2,'1234', '1234' UNION ALL
    SELECT 3,'Email','[email protected]' UNION ALL
    SELECT 4,'12345','12345' UNION ALL
    SELECT 5,'123456',  '123456' UNION ALL
    SELECT 6,'Email','[email protected]' UNION ALL
    SELECT 7,'1234567',  '1234567' UNION ALL
    SELECT 8,'12345678', '12345678' UNION ALL
    SELECT 9,'123456789','123456789' UNION ALL
    SELECT 10,  'Email','[email protected]' UNION ALL
    SELECT 11,  '01','01' UNION ALL
    SELECT 12,  'Email','[email protected]' UNION ALL
    SELECT 13 , '012',  '012' UNION ALL
    SELECT 14  ,'Email','[email protected]' UNION ALL
    SELECT 15  ,'0123', '0123'
),
---temporary table to hold row numbers
tbl([Row_Num], [id], [type]) AS
(
    SELECT (ROW_NUMBER() OVER (ORDER BY [id])) AS [Row_Num] ,[id],[type] FROM data 
)

---actual query using both tables
SELECT 
    d.[id], 
    d.[type], 
    d.[details] 
FROM [data] d 

INNER JOIN

[tbl] t

ON d.[id] = t.[id] 

WHERE t.[Row_Num] IN (SELECT Row_Num + 1 FROM tbl WHERE [type] = 'Email')

Upvotes: 0

Darshak
Darshak

Reputation: 867

For SQL Server Query like this 

select * from table where id in (Select id+1 from Table where type='Email')

Upvotes: 1

Conrad
Conrad

Reputation: 33

If you are specifically wanting just the first record after the email row you could be a bit more selective like this:

SELECT * FROM Table WHERE ID IN (SELECT ID+1 FROM Table where type='Email')

Upvotes: 1

Related Questions