SQL Group by query to select the data from same table

Question

My table have following data,

ID name  devID
1  abc    101
2  def    111
3  ghi    121
4  abc    102
5  def    110

I want to select the rows (ID,name,devID)based on the following condition:

a. the value of devID for name abc have been increased by 1, so only higher value record should be displayed in the result (only 102)

b. the value of devID for name def have been decrease by 1, it should display all records (111 and 110)

Also we will be keep on adding the records for different rows and each name will not have more than 2 or max 3 rows in the table, so above condition should be always true.

Please help me on this query. Thanks in Advance.

Answer 1

I used an incremental approach. I didn't really see another option. This returns what you need I believe:

create table #t1
(
    ID int identity,
    name varchar(3),
    devID int
)

insert into #t1(name,devID)
values('abc',101),('def',111),('ghi',121),('abc',102),('def',110)


create table #t2
(
    ID int,
    name varchar(3),
    devID int
)

declare @count int = 1,
    @name1 varchar(3)
while @count <= (select MAX(ID) from #t1)
begin--1
    set @name1 = (select name from #t1 where ID = @count)
    if (@name1 not in (select distinct name from #t2)) or ((select devID from #t1 where ID = @count) < (select devID from #t2 where name = @name1))
    begin--2
        insert into #t2
            select *
            from #t1
            where ID = @count
    end--2
    else
    begin--2
        update #t2
            set devID = (select devID from #t1 where ID = @count)
            where name = @name1
    end--2

    set @count+=1
end--1

select *
from #t2

drop table #t1
drop table #t2

EDIT: Results:

ID          name devID
----------- ---- -----------
1           abc  102
2           def  111
3           ghi  121
5           def  110

(4 row(s) affected)