How to select row index in MSSQL

Question

I have a table that contains three fields: Top(int), Left(int), Text(varchar).

The data inside the table are as following:

Text      X       Y

1       1000    250

2        700    100

A        618    300

B        620    400

C        625    405

F        623    402

D        400    410

M        300    415

Z        304    418

I want to get all the "Text" where (X - previous X) < 10 and (Y - previous Y) < 10

The result in this case should be: B, C, F, M, Z

Is it possible to to do that with SQL?

Thank you in advance

Have you tried any SQL yet? If yes then paste it here so someone can help you out. — Syfer, Jul 27 '17 at 08:53
See - https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement — DanieleO, Jul 27 '17 at 08:57

Kannan Kandasamy · Answer 1 · 2017-07-27T09:48:30.713

1

You can use lag if it is SQL Server >= 2012 as below:

Select * from (
    Select *,PrevX = lag(X) over(order bY [Text]),
         PrevY= lag(Y) over(order by [Text]) from yourtable
 ) a
 where a.x-a.prevx < 10 and a.y-a.prevy <10

But here order by column needs to be correct. For sure you will be having an identity or sort column which you can use in order by

for Sql Server 2008 you can try as below:

;With cte as (
    Select *,RowN = Row_Number() over(order bY [Text])) from yourtable
 ) Select * from cte c1
 left Join cte c2
 on c1.RowN = C2.RowN-1
 where c1.x-c2.X < 10 and C1.y-c2.y <10

edited Jul 27 '17 at 09:48

answered Jul 27 '17 at 09:02

Kannan Kandasamy

13,405
3
25
38

I think is good idea but right now I'm using SQL SERVER 2008 R2, actually is good motivation to upgrade to MSSQL 2012 and try it – Waleed ELerksosy Jul 27 '17 at 09:28
If sql server 2008 you need to do row_number and do self join with row_number -1 – Kannan Kandasamy Jul 27 '17 at 09:29
Okay I will create row_number field and try, and let you know the resualt – Waleed ELerksosy Jul 27 '17 at 09:35
Updated my query which is not tested but in similar lines it will work – Kannan Kandasamy Jul 27 '17 at 09:40
Thank you, I will try it :) – Waleed ELerksosy Jul 27 '17 at 09:45
Is working, thank you so much! I've an issue with the resualt because appear like this: Text, O_Top, O_Left, ID, Text, O_Top, O_Left, ID I want to do like union to combine each the two fields similar together in one field – Waleed ELerksosy Jul 28 '17 at 20:12

How to select row index in MSSQL

1 Answers1