Firebird and SQL Server delete highest rows in groups

Question

I need to delete the 3rd and greater records, sorted by product, type and display order, from the following tables:

Stockist table:

CREATE TABLE INVENTORYWEBSTOCKISTS
(
  STOCKISTID     NUMERIC(10,0) NOT NULL,
  PRODUCTID  NUMERIC(10,0) NOT NULL,
  DISPLAYORDER   NUMERIC(10,0),
  CUSTOMERID     NUMERIC(10,0),
CONSTRAINT PK_INVENTORYWEBSTOCKISTS PRIMARY KEY (STOCKISTID)
);

STOCKISTID is the unique autoinc column for the table and can be used as an ID. PRODUCTID and CUSTOMERID both reference other tables.

Customer table:

CREATE TABLE CUSTOMERS 
(
  CUSTOMERID     NUMERIC(10,0) NOT NULL,
  WEBSTOCKISTSTOCKISTTYPE    VARCHAR(100),
CONSTRAINT PK_CUSTOMERS PRIMARY KEY (CUSTOMERID)
);

Customers table has records:

insert into CUSTOMERS (CUSTOMERID, WEBSTOCKISTSTOCKISTTYPE) values (1, 'Reseller');
insert into CUSTOMERS (CUSTOMERID, WEBSTOCKISTSTOCKISTTYPE) values (2, 'Reseller');
insert into CUSTOMERS (CUSTOMERID, WEBSTOCKISTSTOCKISTTYPE) values (3, 'Reseller');
insert into CUSTOMERS (CUSTOMERID, WEBSTOCKISTSTOCKISTTYPE) values (4, 'Installer');
insert into CUSTOMERS (CUSTOMERID, WEBSTOCKISTSTOCKISTTYPE) values (5, 'Installer');
insert into CUSTOMERS (CUSTOMERID, WEBSTOCKISTSTOCKISTTYPE) values (6, 'Installer');
insert into CUSTOMERS (CUSTOMERID, WEBSTOCKISTSTOCKISTTYPE) values (7, 'Installer');

Stockist table has records:

insert into INVENTORYWEBSTOCKISTS (STOCKISTID, PRODUCTID, DISPLAYORDER, CUSTOMERID) values (1, 1, -100, 1);
insert into INVENTORYWEBSTOCKISTS (STOCKISTID, PRODUCTID, DISPLAYORDER, CUSTOMERID) values (2, 1, -101, 2);
insert into INVENTORYWEBSTOCKISTS (STOCKISTID, PRODUCTID, DISPLAYORDER, CUSTOMERID) values (3, 1, -102, 3);
insert into INVENTORYWEBSTOCKISTS (STOCKISTID, PRODUCTID, DISPLAYORDER, CUSTOMERID) values (4, 1, -103, 4);
insert into INVENTORYWEBSTOCKISTS (STOCKISTID, PRODUCTID, DISPLAYORDER, CUSTOMERID) values (5, 1, -104, 5);
insert into INVENTORYWEBSTOCKISTS (STOCKISTID, PRODUCTID, DISPLAYORDER, CUSTOMERID) values (6, 1, -105, 6);
insert into INVENTORYWEBSTOCKISTS (STOCKISTID, PRODUCTID, DISPLAYORDER, CUSTOMERID) values (7, 1, -106, 7);
insert into INVENTORYWEBSTOCKISTS (STOCKISTID, PRODUCTID, DISPLAYORDER, CUSTOMERID) values (10, 2, -107, 3);
insert into INVENTORYWEBSTOCKISTS (STOCKISTID, PRODUCTID, DISPLAYORDER, CUSTOMERID) values (13, 2, -108, 6);
insert into INVENTORYWEBSTOCKISTS (STOCKISTID, PRODUCTID, DISPLAYORDER, CUSTOMERID) values (14, 2, -109, 7);

For the combined results:

select
    INVENTORYWEBSTOCKISTS.STOCKISTID,
    INVENTORYWEBSTOCKISTS.PRODUCTID,
    INVENTORYWEBSTOCKISTS.DISPLAYORDER,
    CUSTOMERS.WEBSTOCKISTSTOCKISTTYPE
from
    INVENTORYWEBSTOCKISTS
left join
    CUSTOMERS on CUSTOMERS.CUSTOMERID = INVENTORYWEBSTOCKISTS.CUSTOMERID
order by
    INVENTORYWEBSTOCKISTS.PRODUCTID,
    CUSTOMERS.WEBSTOCKISTSTOCKISTTYPE,
    INVENTORYWEBSTOCKISTS.DISPLAYORDER

Output:

STOCKISTID, PRODUCTID, DISPLAYORDER, WEBSTOCKISTSTOCKISTTYPE
7,  1, -106, Installer
6,  1, -105, Installer
5,  1, -104, Installer
4,  1, -103, Installer
3,  1, -102, Reseller
2,  1, -101, Reseller
1,  1, -100, Reseller
14, 2, -109, Installer
13, 2, -108, Installer
10, 2, -107, Reseller

I need to delete the 3rd and above record for each product/stockist type group ordered by display order, so would expect it to delete rows 5, 4, and 1.

I have tried heaps of different queries from here and the web generally, I can't find anything that will group and order for the delete and work on both MS SQL and Firebird 1.5.

SQL Fiddle to experiment with: http://sqlfiddle.com/#!3/7101de/1

So after deleting, the table should look like:

Output:

STOCKISTID, PRODUCTID, DISPLAYORDER, WEBSTOCKISTSTOCKISTTYPE
7,  1, -106, Installer
6,  1, -105, Installer
3,  1, -102, Reseller
2,  1, -101, Reseller
14, 2, -109, Installer
13, 2, -108, Installer
10, 2, -107, Reseller

The delete should be executed as one query, so I can pass it to the server.

*** Edit:

Maybe to simplify, if I can do a select command that returns the STOCKISTID's to be deleted, I can then execute the delete commands seperately.

*** Edit 2:

As a test, I added the stockist type field to the INVENTORYWEBSTOCKISTS table, and can run this query:

SELECT INVENTORYWEBSTOCKISTS.STOCKISTID
FROM   INVENTORYWEBSTOCKISTS IWS1
WHERE
(SELECT COUNT(*)
FROM INVENTORYWEBSTOCKISTS  IWS2
WHERE IWS2.PRODUCTID = IWS1.PRODUCTID
AND IWS2.STOCKISTTYPE = IWS1.STOCKISTTYPE
AND IWS2.DISPLAYORDER <= IWS1.DISPLAYORDER) > 2

and return the correct fields (ie the ones that should be deleted). This was based on a question asked previously on SO.

As soon as I try and link in the CUSTOMER table as the query should be, it gives different results, less rows. Maybe someone might be able to help with that?

Answer 1

Try this, I have used ROW_NUMBER to eliminate your case (From what I understood from your question)

    ;WITH CTE AS(
    SELECT ROW_NUMBER()OVER(PARTITION BY PRODUCTID,WEBSTOCKISTSTOCKISTTYPE
                          ORDER BY (SELECT 1) )SNO, INVENTORYWEBSTOCKISTS.*,
                         CUSTOMERS.WEBSTOCKISTSTOCKISTTYPE FROM
    INVENTORYWEBSTOCKISTS
    LEFT JOIN
      CUSTOMERS ON INVENTORYWEBSTOCKISTS.CUSTOMERID = CUSTOMERS.CUSTOMERID
     )

     SELECT * FROM CTE WHERE SNO <3 

Edit: From your comments:

    ;WITH CTE AS(
    SELECT ROW_NUMBER()OVER(PARTITION BY PRODUCTID,WEBSTOCKISTSTOCKISTTYPE
        ORDER BY (SELECT DISPLAYORDER) )SNO, INVENTORYWEBSTOCKISTS.*,
        CUSTOMERS.WEBSTOCKISTSTOCKISTTYPE FROM
    INVENTORYWEBSTOCKISTS
    LEFT JOIN
    CUSTOMERS ON INVENTORYWEBSTOCKISTS.CUSTOMERID = CUSTOMERS.CUSTOMERID
    )
    SELECT STOCKISTID FROM CTE WHERE SNO>2

If it is a problem with CTE

    DELETE FROM INVENTORYWEBSTOCKISTS WHERE STOCKISTID IN ( 

    SELECT STOCKISTID FROM (
    SELECT ROW_NUMBER()OVER(PARTITION BY PRODUCTID,WEBSTOCKISTSTOCKISTTYPE
                ORDER BY (SELECT DISPLAYORDER) )SNO, INVENTORYWEBSTOCKISTS.*,
                CUSTOMERS.WEBSTOCKISTSTOCKISTTYPE FROM
            INVENTORYWEBSTOCKISTS
            LEFT JOIN
            CUSTOMERS ON INVENTORYWEBSTOCKISTS.CUSTOMERID = CUSTOMERS.CUSTOMERID
            ) AS A WHERE SNO>2
            )