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?

Firebird and SQL Server delete highest rows in groups

1 Answers