BAQ - choose 2nd value

Hi All,
I have been using FIRST_VALUE and LAST_VALUE.
But if my results have many values and i like to select the 2nd value.
How do i do that?

For e.g. I have a calculated field that gives me the 1st bin number if a part exists in multiple bins using:
FIRST_VALUE(PartBin.BinNum) over (Order by PartBin.PartNum)

How do i get the 2nd value?

Cheers
Hymal7

post your query phrase. You can add a row number as a calculated field.

Then you should be able to grab row 2 of the group.

So I have this BAQ with UnionAll subquery, the top query for primary bins and 2nd subquery for specific secondary bins. [This 2nd subquery can be ignored]
I have put a filter where I only get 1 line showing the last value.

select
[Part].[PartNum] as [Part_PartNum],
(case
when SubQuery1.PartBin_BinNum = PlantWhse.PrimBin then ‘0’
else SubQuery1.PartBin_BinNum
end) as [Calculated_Bins],
[SubQuery1].[Calculated_OnHand] as [Calculated_OnHand],
[SubQuery1].[Calculated_MainBin] as [Calculated_MainBin]
from Erp.Part as Part
left outer join (select
[PartBin].[PartNum] as [PartBin_PartNum],
[PartBin].[BinNum] as [PartBin_BinNum],
(sum(PartBin.OnhandQty)) as [Calculated_OnHand],
(LAST_VALUE(PartBin.BinNum) over (Order by PartBin.PartNum)) as [Calculated_MainBin]
from Erp.PartBin as PartBin
where (PartBin.WarehouseCode = ‘main’ and not PartBin.BinNum in (’#default#’, ‘desp’, ‘insp’, ‘spares’, ‘van’, ‘wip’, ‘zvan’) and PartBin.BinNum is not null)
group by [PartBin].[PartNum],
[PartBin].[BinNum]) as SubQuery1 on
Part.PartNum = SubQuery1.PartBin_PartNum
left outer join Erp.PlantWhse as PlantWhse on
Part.Company = PlantWhse.Company
and Part.PartNum = PlantWhse.PartNum
and ( PlantWhse.WarehouseCode = ‘main’ )

where (Part.PartNum in (‘30030/g/st’))
and SubQuery1.PartBin_BinNum is not null and not (case
when SubQuery1.PartBin_BinNum = PlantWhse.PrimBin then ‘0’
else SubQuery1.PartBin_BinNum
end) = ‘0’ and SubQuery1.Calculated_MainBin = Calculated.Bins

union all
select
[Part_3].[PartNum] as [Part_3_PartNum],
(case
when SubQuery4.PartBin_3_BinNum = PlantWhse_3.PrimBin then ‘0’
else SubQuery4.PartBin_3_BinNum
end) as [Calculated_Bins_3],
[SubQuery4].[Calculated_OnHand_3] as [Calculated_OnHand_3],
(Bins_3) as [Calculated_MainBin_3]
from Erp.Part as Part_3
left outer join (select
[PartBin_3].[PartNum] as [PartBin_3_PartNum],
[PartBin_3].[BinNum] as [PartBin_3_BinNum],
(SUM(PartBin_3.OnhandQty)) as [Calculated_OnHand_3]
from Erp.PartBin as PartBin_3
where (PartBin_3.WarehouseCode = ‘main’ and PartBin_3.BinNum in (’#default#’, ‘desp’, ‘insp’, ‘spares’) and PartBin_3.BinNum is not null)
group by [PartBin_3].[PartNum],
[PartBin_3].[BinNum]) as SubQuery4 on
Part_3.PartNum = SubQuery4.PartBin_3_PartNum
left outer join Erp.PlantWhse as PlantWhse_3 on
Part_3.Company = PlantWhse_3.Company
and Part_3.PartNum = PlantWhse_3.PartNum
and ( PlantWhse_3.WarehouseCode = ‘main’ )

where (Part_3.PartNum in (‘30030/g/st’))
and not (case
when SubQuery4.PartBin_3_BinNum = PlantWhse_3.PrimBin then ‘0’
else SubQuery4.PartBin_3_BinNum
end) = ‘0’

group by [Part_3].[PartNum],
(case
when SubQuery4.PartBin_3_BinNum = PlantWhse_3.PrimBin then ‘0’
else SubQuery4.PartBin_3_BinNum
end),
[SubQuery4].[Calculated_OnHand_3],
(Bins_3)

which part do we want to grab the second item from?

here is an example of showing what we have done here. Part 000-010 had two items. You can then query those items.

ok thanks for the suggestion.
i did use Row_number() initially but deleted it because i didnt put part number as a filter in the partbin subquery so the rownumber i got was in thousands instead of starting from 1.
it works now.
Thanks again