SQL Server Group By and First in priority

Question

I have a data table

SQL Fiddle

Schema

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', 1, 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');

I want to group by Link ID and pick up only the one with minimum of priority if the status is PENDING If the group by data has other status e.g. PROCESSING then it should simply ignore that group Only if all the status is either PENDING excluding the group if there is one with PROCESSING status

If I was to run the query it would only return Link ID 001 and 002

If information is important to the question, please include it in the question, not an off-site resource. The fiddle doesn't load for me, so is not helpful, and someone with almost 17k reputation should know: [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) — Thom A, Feb 06 '23 at 18:30
The off-site is provided by stackoverflow itself and used all the time. Nothing new. I have attached the Schema and samples data inserts I have provided enough information as required — Makky, Feb 06 '23 at 18:33
*"The off-site is provided by stackoverflow itself"* sqlfiddle has no affliation with [so]. — Thom A, Feb 06 '23 at 18:34
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Feb 06 '23 at 18:35
@Larnu . The only trick part in my case is that I want to skip the group by linkID if the if any of the rows has status "PROCESSING" — Makky, Feb 06 '23 at 18:36

score 1 · Answer 1 · answered Feb 06 '23 at 18:58

1

Your description is hard to make sense of but given your expectation perhaps this is what you require?

select LinkId
from lenderdata
group by LinkId
having Min(status) = 'Pending' and Max(Status) = 'Pending';

answered Feb 06 '23 at 18:58

Stu

30,392
6
14
33

score 1 · Answer 2 · answered Feb 07 '23 at 06:34

You could use the following where clause to filter out any LinkID group that has a status <> 'pending':

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

Now, according to your request: (I want to group by Link ID and pick up only the one with a minimum priority)

If you meant to select rows with a minimum priority for each LinkID group, then you may use the row_number function approach.

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

If you meant to select rows with a minimum priority among all LinkID groups, then you may use the rank function approach.

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

See a demo for your sample data.

See a demo for modified sample data to see the difference between the two approaches (your sample data will get you the same results for both approaches).

score -1 · Accepted Answer · answered Jul 09 '23 at 18:12

SELECT ld.LinkID, ld.Lender, ld.Item, ld.Priority, ld.Quantity, ld.Status
FROM lenderdata ld
INNER JOIN (
    SELECT LinkID, MIN(Priority) as MinPriority
    FROM lenderdata
    WHERE Status = 'PENDING'
    GROUP BY LinkID
    HAVING COUNT(DISTINCT Status) = 1
) groupedLd
ON ld.LinkID = groupedLd.LinkID AND ld.Priority = groupedLd.MinPriority
WHERE ld.Status = 'PENDING'

This is the right answer. Got it from ....

SQL Server Group By and First in priority

3 Answers3