4

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

1 Answer 1

2

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.

2
  • 1
    That's how I made it a view. Thank you. fiddle
    – omerix
    Oct 1, 2022 at 13:45
  • @omerix Great, no problem!
    – J.D.
    Oct 1, 2022 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.