Ken Adeniji's Blog

/*
Two tables, identical schema, two columns only, table A and table B.
First column is product number, second column is price.
Table A Product 1, $20. Product 2, $20.
Table B Product 2, $30. Product 3, $20.
For each product, select the maximum price.
*/

CREATE TABLE TableA
(
ProductNumber     VARCHAR(20) NOT NULL,
Price        DECIMAL
)
GO

CREATE TABLE TableB
(
ProductNumber     VARCHAR(20) NOT NULL,
Price        DECIMAL
)
GO

INSERT INTO TableA
(
ProductNumber,
Price
)
VALUES
(
‘Product 1’,
20
)
GO

INSERT INTO TableA
(
ProductNumber,
Price
)
VALUES
(
‘Product 2’,
20
)
GO

INSERT INTO TableB
(
ProductNumber,
Price
)
VALUES
(
‘Product 2’,
30
)
GO

INSERT INTO TableB
(
ProductNumber,
Price
)
VALUES
(
‘Product 3’,
20
)
GO

CREATE PROCEDURE TwoIdenticalTablesMaxPrice
AS
–For each product, select the maximum price.

–Declare work table variable.
DECLARE @MultipleTablesGrouping AS Table(ProductNumber VARCHAR(20), Price DECIMAL)

–Populate work table variable.
INSERT INTO @MultipleTablesGrouping
SELECT
ProductNumber,
Max(Price)
FROM
TableA
GROUP BY
ProductNumber
UNION
SELECT
ProductNumber,
Max(Price)
FROM
TableB
GROUP BY
ProductNumber

–Declare work table variable.
DECLARE @SingleTableMaximumPrice AS Table(ProductNumber VARCHAR(20), Price DECIMAL)

INSERT INTO @SingleTableMaximumPrice
SELECT
ProductNumber,
Max(Price)
FROM
@MultipleTablesGrouping
GROUP BY
ProductNumber

–Display result
SELECT * FROM @SingleTableMaximumPrice
GO

EXEC TwoIdenticalTablesMaxPrice
GO

DROP PROCEDURE TwoIdenticalTablesMaxPrice
GO

DROP TABLE TableA
GO

DROP TABLE TableB
GO