Creating a new view or table from the last record

Question

I have 2 tables DraftProducts and Products.

The only difference between the 2 tables is the "phase" field.

I have productCode varchar(20), phase(tinyint), Name, Price, SpecialCells.... etc fields in my DraftProducts table.

the last phase is actually 1 record in the Product table for me.

Until now, when 1 user said that the draft work was finished, I was automatically recording the last phase in the Product table.

But when the last draft changes, it needs to be updated again. It needs to be deleted when deleted etc. a lot of additional work is going on.

In my database, some tables work from the DraftProducts table and some tables from the Products table.

But can I do this as "Products view" independent of the user?

How can I create a "ProductsView" with max(phase) 1 record? How should I do group by or distinct?

As a result of this table, the Products table or view I want should be like this.

p1=8 price, category, description etc. p2=15 price, category, description etc. p3=22 price, category, description etc.

CREATE TABLE [dbo].[DraftProducts](
    [productCode] [varchar](20) NULL,
    [phase] [varchar](50) NULL,
    [name] [varchar](50) NULL,
    [category] [varchar](20) NULL,
    [description] [varchar](20) NULL,
    [price] float NULL
)

CREATE UNIQUE CLUSTERED INDEX [DraftProductsIndex1] ON [dbo].[DraftProducts]
(
    [productCode] ASC,
    [phase] ASC
)

insert into DraftProducts (productCode,phase,name)
values 
('p1',1,'aaaaaaaaaaaaaaa'),
('p2',2,'aaaaaaaaaaaaaaa'),
('p3',1,'aaaaaaaaaaaaaaa'),
('p2',15,'bbbbbbbbbbbbbbb'),
('p3',22,'bbbbbbbbbbbbbbb'),
('p1',8,'bbbbbbbbbbbbbbbbbbbbbbbbb'),
('p2',7,'ccccccccccccccc')

7 rows affected

select * from DraftProducts 

/*
finish phase
p1=8
p2=15
p3=22
*/

productCode	phase	name	category	description	price
p1	1	aaaaaaaaaaaaaaa	null	null	null
p1	8	bbbbbbbbbbbbbbbbbbbbbbbbb	null	null	null
p2	15	bbbbbbbbbbbbbbb	null	null	null
p2	2	aaaaaaaaaaaaaaa	null	null	null
p2	7	ccccccccccccccc	null	null	null
p3	1	aaaaaaaaaaaaaaa	null	null	null
p3	22	bbbbbbbbbbbbbbb	null	null	null

fiddle

Answer 1

What you're looking for is a window function, particularly a ranking window function such as ROW_NUMBER() like so:

WITH _DraftProductsSorted AS
(
    SELECT 
        productCode,
        phase,
        [name],
        category,
        [description],
        price,
        ROW_NUMBER() OVER -- Generates a unique sequential ID within each partition of productCode sorted by the phase
        (
            PARTITION BY productCode 
            ORDER BY phase DESC
        ) AS PartitionSortId
    FROM DraftProducts
)

SELECT 
    productCode,
    phase,
    [name],
    category,
    [description],
    price
FROM _DraftProductsSorted
WHERE PartitionSortId = 1; -- Only return the latest row of each productCode

All you have to do is slap that inside a view, e.g. ProductsView, and anytime the phase changes for a particular productCode the view will automatically show the latest version.

Window functions are very helpful for solving a number of problems. I highly suggest reading through the links I provided. Other window functions like the value functions such as FIRST_VALUE() and LAST_VALUE() can also be used as an alternative way to solve your problem, but would've required more code and I think would've been less performant than the solution I provided above using ROW_NUMBER().

Speaking of performance, while I doubt you'll have any issues as is, if you want to make a slight change to your clustered index like the following, it'll be slightly more suitable to the solution I provided above:

CREATE UNIQUE CLUSTERED INDEX [DraftProductsIndex1] ON [dbo].[DraftProducts]
(
    [productCode] ASC,
    [phase] DESC
);

Notice I changed the order it persists phase to DESC (descending) so it exactly matches the ORDER BY clause of the ROW_NUMBER() function in my solution above.