Using LAST in PSQL

[JordanCN]

I have a table that holds a Bill of Material structure for an application. Fields are Parent, Component, EffectiveDate, Qty. The table of data looks like this:

Car - STDWheel - 1/1/2000 - 4
Car - STDWheel - 6/6/2005 - 0
Car - MAGWheel - 6/6/2005 - 4
Car - GoodTire - 1/1/2000 - 4
Car - GoodTire - 6/6/2005 - 0
Car - GreatTire - 6/6/2005 - 4

The way the application uses its data in this example shows that on 1/1/2000 the Car was made of 4 STDWheels and 4 GoodTires. On 6/6/2005 the StdWheels and GoodTires were replaced with MAGWheels and GreatTires. The reason the STDWheels and GoodTires have the 0 qty record is to show that these items are to start showing on the materials list on 1/1/2000 and stop showing on 6/6/2006.

In MS Access or MS SQL if i wanted to get the latest version of the parts list I would do the following:

Select Last(Parent),
Last(Component),
Last(EffectiveDate),
Last(QTY)
From ProductStructure
Where Parent = 'MyPart'
Order by Component, EffectiveDate

Which would return
Car - STDWheel - 6/6/2005 - 0
Car - MAGWheel - 6/6/2005 - 4
Car - GoodTire - 6/6/2005 - 0
Car - GreatTire - 6/6/2005 - 4

(Note: the 0's are OK because they are eliminated in another step)

My problem is that it looks like PSQL 2000i does not have a LAST option or I am just not getting the syntax correct.

 

[mirtheil]

LAST is not supported by PSQL. I'm not sure how you would get that return. You might look at DISTINCT but I'm not sure if that'll help.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[JordanCN]

Distinct will not work in this case. Neither would Group By which I have also tried. The problem with the Grouping is that I can get the last Effective Date in my example, but adding the Qty natrually causes all parts to show because the Qtys are unique.

I have been avoiding doing the query in two parts because it is very slow and I loose some versitility that I was hoping that I might be able to use in the future.

I know I could create a view that has that last Effective Date of each relationship and link that back to the table on Parent/Component/EffDate after but that query takes too long to run.

 

[foxy69]

This would work but not in every case, the top 4 may only need to be the top 2 depending on th enumber of changes.

select top 4 part,component,effectivedate,qty from productstructure where Where Parent = 'MyPart'
Order by Component, EffectiveDate desc

I think (if I understand correctly what you are doing) I would create 2 extra fields. One marked as 'componentactive' which would be true/false. This would give you a current build list. Secondly, create a 'revision' field, this would increment with each change and would give you an historical log of all changes.

Repoting on 'componentactive' would give you the latest build list, yet reporting by 'revision' would give you the historical list. (Just my idea's!!)

 

[JordanCN]

I can't do that because I do not know what the total number of parts is going to be. Also the data structure on the back end is not something I can adjust.

I am just going to stick with MS Access on this one.

 

[Linda84]

Just to close this out, I believe the following self-join will work:

SELECT * FROM ProductStructure PS1
WHERE PS1.EffectiveDate =
(SELECT MAX(PS2.EffectiveDate) from ProductStructure PS2
WHERE PS2.parent = PS1.parent
AND PS2.component = PS1.component)

Linda
Pervasive Software