CODEWITHSUNDEEP
sqlsql-servergroup-by
Makky
Makky

Reputation: 17463

SQL Server Group By and First in priority

I have a data table

SQL Fiddle

http://sqlfiddle.com/#!18/b33c86

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

enter image description here

Upvotes: 0

Views: 170

Answers (3)

Makky
Makky

Reputation: 17463

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 ....

Upvotes: -1

ahmed
ahmed

Reputation: 9181

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).

Upvotes: 1

Stu
Stu

Reputation: 32579

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';

Upvotes: 1

Related Questions