Help with Selecting Next Row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Selecting Next Row

Author

Topic

danielgl85
Starting Member

5 Posts

Posted - 2008-09-26 : 14:56:24

Hi I have this table from which I want to SELECT the ROW that follows any ROW that has a specific word in it.

For example:

column
-------
7519852
employee_1
user: abc
attended
user: 123
not attended
user: not found
employee not found

From the table above, I want to first scan the table and look for "user:%" Once I've identified the rows with "user:%" in them, I want to SELECT the NEXT rows. Meaning: attended, not attended, employee not found.

Is there a function to SELECT the NEXT row?

Thanks in advance for any help I might get!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 15:06:10

Yes there is.
How do you identify order of records?

E 12°55'05.63"
N 56°04'39.26"

danielgl85
Starting Member

5 Posts

Posted - 2008-09-26 : 15:18:21

Thanks for your quick response!

That's the problem. The only way I know what ROWS I need to select is by looking at the previous row (if it is one that has "user%" in it.)

I'm using the following the get the USER NAMES (abc, 123, not found):

SELECT RIGHT(column, LEN(column) - CHARINDEX(':', column)) AS user_nm
FROM column
WHERE column LIKE 'user:%'

But now I need to get what comes right after these user names. That, is, whatever is populated in the next row for each individual case.

Once again, thanks for your help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 16:50:34

Next and previous "row" is not defined in a relational database, unless there is some column to define ORDER BY.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 02:19:45

what's the primary key of your table?

danielgl85
Starting Member

5 Posts

Posted - 2008-09-29 : 09:18:43

There is no primary key. Although I could insert a 2nd column, which would have only the row numbers (with the identity keyword used when creating the table)

By any chance do you know if FETCH NEXT would work(not too familiar with the concept behind it)? Or do you have any other suggestions?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 09:51:28

quote:
Originally posted by danielgl85

There is no primary key. Although I could insert a 2nd column, which would have only the row numbers (with the identity keyword used when creating the table)

By any chance do you know if FETCH NEXT would work(not too familiar with the concept behind it)? Or do you have any other suggestions?

Thanks!

which is column having identity defined?

danielgl85
Starting Member

5 Posts

Posted - 2008-09-29 : 09:59:56

id# column1
---- -------
1 7519852
2 employee_1
3 user: abc
4 attended
5 user: 123
6 not attended
7 user: not found
8 employee not found

This is what it looks like. I just want to SELECT the rows that come right after any rows with "user:" in it (in this case 4, 6, 8)

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 10:08:38

quote:
Originally posted by danielgl85

id# column1
---- -------
1 7519852
2 employee_1
3 user: abc
4 attended
5 user: 123
6 not attended
7 user: not found
8 employee not found

This is what it looks like. I just want to SELECT the rows that come right after any rows with "user:" in it (in this case 4, 6, 8)

Thanks!

SELECT t1.*
FROM YourTable t1
INNER JOIN (SELECT ID FROM YourTable WHERE column1 LIKE 'user:%')t2
ON t1.ID=t2.ID+1

danielgl85
Starting Member

5 Posts

Posted - 2008-09-29 : 10:46:16

THANK YOU VERY MUCH!

IT DID WORK!

I REALLY APPRECIATE YOUR HELP!

BEST REGARDS,

DANIEL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 11:15:15

quote:
Originally posted by danielgl85

THANK YOU VERY MUCH!

IT DID WORK!

I REALLY APPRECIATE YOUR HELP!

BEST REGARDS,

DANIEL

]

welcome

Ameena
Starting Member

1 Post

Posted - 2010-10-22 : 15:02:11

wat if the primary key is not in sequence?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-23 : 02:18:21

quote:
Originally posted by Ameena

wat if the primary key is not in sequence?

still this will work


SELECT t1.*
FROM  (SELECT ID FROM YourTable WHERE column1 LIKE 'user:%')t2
CROSS APPLY (SELECT TOP 1 *
             FROM Table
             WHERE ID > t2.ID
             ORDER BY ID ASC) t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources