0

lets say I have the following tables:

Cars
--------
ID  Make    Model       Version 
1   Honda   Accord      v1
2   Honda   Civic       v1
3   Honda   Odysey      v1
1   Honda   AccordLX    v2
1   Honda   AccordEX    v3

CarProperties
-------------
CarID   key value   Version
1   color   red v1
1   doors   4   v1
2   color   blue    v1
2   doors   2   v1
3   color   black   v1
1   color   blue    v2
1   color   green   v3

Versions
-----------
ID  Status
v1  Approved
v2  Pending
v3  Approved

Basically, I have a entry / properties type pattern that I'm trying to do version management on.

How can I query this to always get the latest approved version of a car with all of its properties? Note that some properties may not be updated in a particular version. ie: in the example below, the approved version of carid:1 is

Honda AccordEX color:green doors:4

Some data coming from V3 and other data coming from V1.

This sounds like there would be a well developed pattern for managing this type of data. Can somebody please point me into the right direction?

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
majestiq
  • 545
  • 8
  • 25
  • 1
    You should change `Version` to a numeric datatype, so you can use functions like `MAX()`. – Barmar Jun 07 '18 at 18:04
  • So you want the maximum version of a property up to the version associated with the car model? – Barmar Jun 07 '18 at 18:06
  • You can start with the techniques in https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1, but you'll have to tweak it to limit the version based on the car version. – Barmar Jun 07 '18 at 18:07

1 Answers1

0

I think this:

WITH car_version_latest AS (
     SELECT c.*, ROW_NUMBER() OVER (PARTITION BY (c.ID, c.Make, c.Model) ORDER BY c.Version DESC) AS rn
     FROM Cars AS c
     )

SELECT * FROM car_version_latest cvl
WHERE cvl.rn = 1;

will work, reasonably well, but it's frankly not clear.

That said, your data model is frankly kind of weird. You have rows with the same ID but changing properties (why isn't make and model in CarProperties?).

Your CarProperties table is (sort of) EAV, but has a duplicated version field that is apparently not needed.

I think you really need to revise your data model and normalize it a bit more.

nimish
  • 4,755
  • 3
  • 24
  • 34