Help with Over Partition in a CTE
Posted on 2013-11-08
Below is a simplified CTE and the result set.
With MstrTable(IDNo, RelNo)
as (Select IDNo, RelNo From TableA where xxx
Select IDNo,RelNo from TableA where yyyy)
Select IDNo,RelNo From MstrTable Order by IDNo, RelNo
Desired result Set
I want the result set to contain only one entry for the IDNo and the MAX RelNo (see IDNo = 3 above).
With a single query in the CTE, without the UNION, Over Partition works great. But I do not think that will work as anticipated with the UNION. How do I get the result set to display a single value for IDNo and the Maximum value for RelNo?