SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table lenderdata
(
    ID int identity
        primary key,
    LinkID varchar(250) null,
    Lender varchar(250) null,
    Item varchar(250) null,
    Priority int null,
    Quantity int null,
    Status varchar(250) null
);

INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('001', 'A', 'Apple', 3, 100, 'PENDING');
INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('001', 'B', 'Orange', 2, 100, 'PENDING');
INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('002', 'C', 'Strawberry', 1, 1000, 'PENDING');
INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('002', 'D', 'grapes', 2, 100, 'PENDING');
INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('003', 'E', 'coffee', 1, 1000, 'PROCESSING');
INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('003', 'F', 'mango', 2, 1000, 'PENDING');
6 rows affected

SELECT * FROM lenderdata
WHERE LinkID NOT IN
(
  SELECT LinkID FROM lenderdata WHERE Status <> 'PENDING'
)


ID
LinkID
Lender
Item
Priority
Quantity
Status


1
001
A
Apple
3
100
PENDING

2
001
B
Orange
2
100
PENDING

3
002
C
Strawberry
1
1000
PENDING

4
002
D
grapes
2
100
PENDING


WITH CTE AS 
(
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY LinkID ORDER BY Priority) rn
  FROM lenderdata
  WHERE LinkID NOT IN
  (
    SELECT LinkID FROM lenderdata WHERE Status <> 'PENDING'
  )
)
SELECT ID, LinkID, Lender, Item, Priority, Quantity, Status
FROM CTE WHERE rn = 1


ID
LinkID
Lender
Item
Priority
Quantity
Status


2
001
B
Orange
2
100
PENDING

3
002
C
Strawberry
1
1000
PENDING


WITH CTE AS 
(
  SELECT *,
    RANK() OVER (ORDER BY Priority) rnk
  FROM lenderdata
  WHERE LinkID NOT IN
  (
    SELECT LinkID FROM lenderdata WHERE Status <> 'PENDING'
  )
)
SELECT ID, LinkID, Lender, Item, Priority, Quantity, Status
FROM CTE WHERE rnk = 1


ID
LinkID
Lender
Item
Priority
Quantity
Status


3
002
C
Strawberry
1
1000
PENDING


SELECT LinkID, MIN(Priority) FROM lenderdata
WHERE LinkID NOT IN
(
  SELECT LinkID FROM lenderdata WHERE Status <> 'PENDING'
)
GROUP BY LinkID


LinkID
(No column name)


001
2

002
1
ID	LinkID	Lender	Item	Priority	Quantity	Status
1	001	A	Apple	3	100	PENDING
2	001	B	Orange	2	100	PENDING
3	002	C	Strawberry	1	1000	PENDING
4	002	D	grapes	2	100	PENDING