Filter on Max value

building semantic layer
1

Hello,

Looking to create a filter for a universe. I searched through and only have been able to find half of what I am after.

What I have is 2 tables

Table A (Main table with further fields that have info)

Main Value, Other fields
A, Further Info
B, Further Info
C, Further Info
D, Further Info

Table B (Sequence Table for Main Table Changes)

Main Value, Sequence, Other fields
A, Seq 1, Details
A, Seq 2, Details
B, Seq 1, Details
B, Seq 2, Details
C, Seq 1, Details
D, Seq 1, Details

Tables are joined on main value (inner join)

What I would like to do is select the Max Sequence within Table B for the first value.

When I write Table B.MainValue = (Select Max(Table B.MainValue) From Table B as a filter, I get the highest sequence for all Main Values. What I would like is the highest sequence for each Main value.

Output eventually looking like (by applying this filter):

A|Description of A|Max Seq|Details of Main Value Change
B|Description of B|Max Seq|Details of Main Value Change
C|Description of C|Max Seq|Details of Main Value Change
D|Description of D|Max Seq|Details of Main Value Change

Any help would prevent a stroke.

dusted (BOB member since 2003-09-18)

2

Hi

This is a common challenge and you’re looking to create a correlated subquery. Further details can be in found in the article Filtering for Latest Data in a Universe

rgds

AL

agulland :uk: (BOB member since 2004-03-17)

3

Thank you.

dusted (BOB member since 2003-09-18)