Apologies for the long post…
I have a dataset imported from Excel… very simple example follows
Headers
EntityType,Code,Name
Values (6 example rows on the same sheet)
Company, 1,J Smith Ltd
null,2, M Brown Products
null3,Family Furnisher Plc
Car,1,Ford
null,2,Nissan
null,3,Honda
So, what I wanted to do was to effectively transform the above into
Company, 1,J Smith Ltd
Company,2, M Brown Products
Company,3,Family Furnisher Plc
Car,1,Ford
Car,2,Nissan
Car,3,Honda
This requires me to find the previous non-null value of column “EntityType”, but also requires me to have a unique identifier. For this I use ROW_NUMBER() OVER()
SELECT ROW_NUMBER() OVER() as RowNum, EntityType,Code,Name
This is then saved.as MyView
I then issue the following to validate…
SELECT RowNum, EntityType,Code,Name From MyView to check which gives me
1,Company, 1,J Smith Ltd
2,null,2, M Brown Products
3,null,3,Family Furnisher Plc
4,Car,1,Ford
5.null,2,Nissan
6,null,3,Honda
I then built the following
SELECT
RowNum,
EntityType,
Code,
Name,
(SELECT EntityType FROM MyView l WHERE l.EntityType IS NOT NULL AND l.RowNum <= v.RowNum ORDER BY l.RowNum DESC LIMIT 1) as LastEntityName
From MyView v
But this doesn’t work, it runs, but doesn’t give me the data I expected, it gave me
Company, 1,J Smith Ltd, null - should be Company
null,2, M Brown Products, null - should be Company
null,3,Family Furnisher Plc, null - should be company
However
If I run
SELECT
RowNum,
EntityType,
Code,
Name,
(SELECT EntityType FROM MyView l WHERE l.EntityType IS NOT NULL AND l.RowNum < v.RowNum ORDER BY l.RowNum DESC LIMIT 1) as LastEntityName
From MyView v
WHERE v.RowNum=3
then the LastEntityName is correct…
There seems to be an issue dealing with a full set… or have I messed up something?