Filter a table based on priory of value in two columns

Question

Post reply

Filter a table based on priory of value in two columns

azdeji

SSCrazy

Points: 2535

March 31, 2022 at 11:58 am

Go to Answer

#4008772

Hi

In the example below, I want to filter a table based on the priority of values in two columns. The priory order is Isowner then IsAuth.

Screenshot 2022-05-16 235831

I greatly appreciate any help you can provide.

SQL

CREATE TABLE tableName 
(
    bp_idvarchar(300),
    customerIdvarchar(300),
    IsOwnervarchar(300),
    IsAuthvarchar(300)
);

INSERT INTO tableName (bp_id,customerId,IsOwner,IsAuth)
VALUES
    ('121', 'John Smith', '1', '0'),
    ('122', 'Mary Johnson', '1', '0'),
    ('123', 'Doris Bean', '0', '0'),
    ('123', 'Doris Bean', '0', '0'),
    ('123', 'Doris Bean', '1', '0'),
    ('124', 'Don Olson', '0', '1'),
    ('125', 'Sam Holder', '0', '0'),
    ('125', 'Sam Holder', '0', '1'),
    ('126', 'Lori Morin', '1', '0'),
    ('127', 'Stan Morris', '0', '1'),
    ('128', 'Jason Thomas', '1', '0'),
    ('128', 'Jason Thomas', '1', '0'),
    ('128', 'John Nell', '1', '0'),
    ('128', 'Jason Thomas', '0', '1'),
    ('128', 'Alex trader', '1', '0'),
    ('128', 'Jason Thomas', '1', '0'),
    ('129', 'Anna', '0', '1'),
    ('129', 'Dan James', '0', '1'),
    ('129', 'Anna', '0', '1'),
    ('129', 'Anna', '0', '0');

This topic was modified 1 year, 11 months ago by azdeji. Reason: Update of sql logic

Phil Parkin

SSC Guru

Points: 246711

April 4, 2022 at 1:49 pm

Answer

#4010519

That's a bit trickier, but try this version and see whether it helps:

WITH ordered
AS
 (SELECT
      hs.Id
  ,   hs.SalesID
  ,   hs.SalesAgent
  ,   hs.Region
  ,   hs.SalesAmount
  ,   hs.IsOwner
  ,   hs.IsAuth
  ,   hs.IsBen
  ,   rn = ROW_NUMBER() OVER (PARTITION BY
                                  hs.SalesID
                              ,   hs.SalesAmount
                              ORDER BY hs.IsOwner DESC
                              ,        hs.IsAuth DESC
                             )
  FROM #HectorSales hs)
SELECT
    ordered.Id
,   ordered.SalesID
,   ordered.SalesAgent
,   ordered.Region
,   ordered.SalesAmount
,   ordered.IsOwner
,   ordered.IsAuth
FROM ordered
WHERE ordered.rn = 1
      AND NOT (ordered.IsOwner = 0 AND ordered.IsAuth = 0)
ORDER BY ordered.Id;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246711 More actions · Answer 1

Does this help? It uses the ROW_NUMBER function to perform the ordering and partitioning that you need.

DROP TABLE IF EXISTS #HectorSales;

CREATE TABLE #HectorSales
(
    Id INT NOT NULL
   ,SalesID INT NULL
   ,SalesAgent VARCHAR(30) NULL
   ,Region VARCHAR(10) NULL
   ,SalesAmount DECIMAL(10, 2) NULL
   ,IsOwner BIT NULL
   ,IsAuth BIT NULL
   ,IsBen BIT NULL
);

INSERT #HectorSales
(
    Id
   ,SalesID
   ,SalesAgent
   ,Region
   ,SalesAmount
   ,IsOwner
   ,IsAuth
   ,IsBen
)
VALUES
(1, 121, 'John Smith', 'West', 78931.01, 1, 0, 0)
,(2, 122, 'Mary Johnson', 'West', 8723412.61, 1, 0, 0)
,(3, 123, 'Doris Bean', 'West', 2000111.67, 0, 0, 1)
,(4, 123, 'Doris Bean', 'South', 2000111.67, 0, 0, 0)
,(5, 123, 'Doris Bean', 'West', 120834.81, 1, 0, 0)
,(6, 124, 'Don Olson', 'West', 508921.48, 0, 1, 0)
,(7, 125, 'Sam Holder', 'East', 8723412.61, 0, 0, 1)
,(8, 125, 'Sam Holder', 'East', 9834212.87, 0, 1, 0)
,(9, 126, 'Lori Morin', 'North', 2000111.67, 1, 0, 0)
,(10, 127, 'Stan Morris', 'East', 4562341.67, 0, 1, 0)
,(11, 128, 'Jason Thomas', 'East', 13424.51, 1, 0, 0)
,(12, 128, 'Jason Thomas', 'East', 22222.22, 0, 1, 0);

WITH ordered
AS (SELECT hs.Id
          ,hs.SalesID
          ,hs.SalesAgent
          ,hs.Region
          ,hs.SalesAmount
          ,hs.IsOwner
          ,hs.IsAuth
          ,hs.IsBen
          ,rn = ROW_NUMBER() OVER (PARTITION BY hs.SalesID ORDER BY hs.IsOwner DESC, hs.IsAuth DESC)
    FROM #HectorSales hs)
SELECT ordered.Id
      ,ordered.SalesID
      ,ordered.SalesAgent
      ,ordered.Region
      ,ordered.SalesAmount
      ,ordered.IsOwner
      ,ordered.IsAuth
FROM ordered
WHERE ordered.rn = 1
ORDER BY ordered.Id;

This reply was modified 2 years, 1 month ago by Phil Parkin. Reason: Improved code. Removed unnecessary CASTs and changed Unicode literals to varchars

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

azdeji SSCrazy Points: 2535 More actions · Answer 2

Thanks Phil, It works perfectly except that sometimes there are multiple SalesIds where IsOwner = 1, but I want only unique values within the duplicate SalesAmounts. See the example below.

SalesId 128 has 6 rows but I only want row 11, 14 and 16 removing duplicate values for SalesAmount 2222 and IsAuth =1

Removing rows 12,13,15

and the same logic would apply to IsAuth also

SalesAmount priority

Thanks

azdeji SSCrazy Points: 2535 More actions · Answer 3

azdeji

SSCrazy

Points: 2535

April 4, 2022 at 3:22 pm

#4010568

It works great, thanks Phil.