CODEWITHSUNDEEP
sqlsql-server
Jim Culverwell
Jim Culverwell

Reputation: 2823

SQL - Nearest record within the same group

I am trying to allocate records of type y a parent id. If there is a record of type x which has the same location, or the location is within 5 less than the type y record, and it is within the same group then I need to allocate that records id as the parent ID. There could be multiple x records which meet this condition so I need to take the nearest.

To be clear I can only set the parentid if the type is Y and the target is type x. Type X's can not have a parentid.

Hopefully these before and after tables will demonstrate what I am trying to do.

Before

enter image description here

After

enter image description here

I was going to do this in code but hopefully it should be possible to do this in SQL. I am using SQL server if that affects the answer?

Greatly appreciate any help.

Cheers

Jim

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

I would approach this with a correlated subquery:

update t
    set ParentId = (select top 1 id
                    from table t2
                    where t2."type" = 'x' and
                          t2."group" = t."group"
                          t2.location between t.location - 5 and t.location
                    order by location
                   )
    from table t
    where "type" = 'y';

Upvotes: 1

Related Questions