-- Collection of code snippets by Arne Vajhøj 
-- (likely posted to comp.os.vms/INFO-VAX, INFO-TPU, MACRO32, eksperten.dk, newz.dk, LinkedIn or other place sometime between 1990 and now) 
CREATE TABLE product (
    id INTEGER NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category INTEGER NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE sale (
    id INTEGER NOT NULL,
    product INTEGER NOT NULL,
    PRIMARY KEY(id)
)
GO

INSERT INTO product VALUES(1, 10.0, 1)
GO
INSERT INTO product VALUES(2, 11.0, 2)
GO
INSERT INTO product VALUES(3, 12.0, 2)
GO
INSERT INTO product VALUES(4, 13.0, 3)
GO
INSERT INTO product VALUES(5, 14.0, 3)
GO
INSERT INTO product VALUES(6, 15.0, 3)
GO
INSERT INTO product VALUES(7, 16.0, 4)
GO
INSERT INTO product VALUES(8, 17.0, 4)
GO
INSERT INTO product VALUES(9, 18.0, 4)
GO
INSERT INTO product VALUES(10, 19.0, 4)
GO

INSERT INTO sale VALUES(1, 1)
GO
INSERT INTO sale VALUES(2, 2)
GO
INSERT INTO sale VALUES(3, 2)
GO
INSERT INTO sale VALUES(4, 3)
GO
INSERT INTO sale VALUES(5, 4)
GO
INSERT INTO sale VALUES(6, 4)
GO
INSERT INTO sale VALUES(7, 5)
GO
INSERT INTO sale VALUES(8, 6)
GO
INSERT INTO sale VALUES(9, 6)
GO
INSERT INTO sale VALUES(10, 7)
GO
INSERT INTO sale VALUES(11, 8)
GO
INSERT INTO sale VALUES(12, 8)
GO
INSERT INTO sale VALUES(13, 9)
GO
INSERT INTO sale VALUES(14, 10)
GO
INSERT INTO sale VALUES(15, 10)
GO

SELECT product.id,product.category,SUM(product.price) totsal
FROM sale JOIN product ON sale.product=product.id
GROUP BY product.id,product.category
ORDER BY totsal DESC
GO

SELECT r.id,r.category,r.totsal
FROM (SELECT g.id,g.category,g.totsal,RANK() OVER (PARTITION BY g.category ORDER BY g.totsal DESC) catrank
      FROM (SELECT product.id,product.category,SUM(product.price) totsal
            FROM sale JOIN product ON sale.product=product.id
            GROUP BY product.id,product.category) g) r
WHERE r.catrank <= 2
ORDER BY r.category,r.totsal DESC
GO

DROP TABLE sale
GO
DROP TABLE product
GO
