1
\$\begingroup\$

dbfiddle

Goal

Without repetition in the code, I want to filter BikeValuationList to only give the BikePriceId of the latest valuation for the BikeID that I have. With this BikePriceId, I then wish to filter BikePrices for the corresponding row.

Schema

CREATE TABLE [BikePrices]
(
  [BikePriceId] INT IDENTITY(1, 1) PRIMARY KEY,
  [PrivateSalePrice] MONEY,
  [ShopPrice] MONEY
);

CREATE TABLE [BikeValuationList]
(
  [BikeID] INT,
  [ValuationCreatedDate] DATETIME,
  [BikePriceId] INT
);

Data

INSERT BikePrices
values (1, 1), (2, 2);

INSERT BikeValuationList
values (1, convert(datetime, '20240101'), 1), (1, convert(datetime, '20250101'), 2);

Code

DECLARE @MyBikeId INT = 1;

SELECT
    [BikePrices].*
FROM
    [BikePrices]
WHERE EXISTS
(
    SELECT
        1
    FROM
    (
        SELECT
            [BikePriceId]
            ,[ValuationCreatedDate]
            ,MAX([ValuationCreatedDate]) OVER () [LastValuationDate]
        FROM
            [BikeValuationList]
        WHERE
            [BikeValuationList].[BikeID] = @MyBikeId        
    ) [BikeLookUp]
    WHERE
        [BikeLookUp].[BikePriceId] = [BikePrices].[BikePriceId]
    AND
        [BikeLookUp].[LastValuationDate] = [BikeLookUp].[ValuationCreatedDate]
);

Restrictions

  1. Do not use a CTE. I have four reasons why they are not suitable in my case, but will not list them because they are not relevant here and will only cause arguments. I promise that it is nothing to do with performance.
  2. I am extremely reluctant to repeat @MyBikeId or introduce new variables or temp tables.
  3. Repetition must be minimised.

My Concerns

Was my solution any good? [LastValuationDate] and its usages are what cause me the most concern. It's not often that I see a semi-join used to filter a table based on its own columns, one of which is a window function's output.

\$\endgroup\$

1 Answer 1

1
\$\begingroup\$

Thank you for the Fiddle, that's very helpful.

primary key

In BikeValuationList you neglected to tell the backend that BikeID is a Primary Key. Every relation should have a PK.

foreign key

CREATE TABLE [BikeValuationList]
(
  ... ,
  ... ,
  [BikePriceId] INT

I don't understand that last line. Wouldn't you like to offer some DDL that explains we have a FK relationship out to the BikePrices table?

singular table name

I'm not crazy about the container ...List suffix of BikeValuationList, since an RDBMS table will always be a container for multiple rows. Recommend you just elide the suffix.

Similarly, consider renaming BikePrices so it is simply the BikePrice relation.

capitalization

Wandering back and forth between FooID and FooId is distracting. Please pick one approach and stick with it.

[identifiers]

You are in control of the identifiers you choose, so you can avoid choosing SQL-92 reserved keywords, or identifiers with embedded blanks and other troublesome characters. Rather than saying

SELECT [select]
FROM [select]

prefer

SELECT sensible_column_name
FROM sensible_table_name

without need to resort to SQL-92 " quoting or vendor-specific [ ] quoting.

JOIN

I guess I just think about the problem differently from how you do. Clearly the EXISTS approach works, but it seems a little roundabout. I definitely value having relations which describe items of interest and which can be independently verified. This lets us build toward a solution via composition. Consider introducing this pair of sub-queries:

CREATE VIEW CurrentValuationTimestamp AS
SELECT BikeID, MAX(ValuationCreatedDate) AS ts
FROM BikeValuationList
GROUP BY BikeID;

CREATE VIEW CurrentValuation AS
SELECT V.*
FROM BikeValuationList V
JOIN CurrentValuationTimestamp TS
ON V.BikeID = TS.BikeID AND V.ValuationCreatedDate = TS.ts;

The first maps each bike to its relevant timestamp. The second filters valuations down to just the relevant valuations.

With those in hand, obtaining the desired result rows becomes trivial.

SELECT P.*, V.BikeID, V.ValuationCreatedDate
FROM BikePrices P
JOIN CurrentValuation V  ON P.BikePriceId = V.BikePriceId
WHERE P.BikeID = @MyBikeId;
\$\endgroup\$

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.