/*
The user will supply the desired vendor ID
so any items that come from that vendor will be listed as the first row per item.
Any subsequent rows would be for the same item from other vendors
but ONLY be the most recent Last_Quoted_Cost.
*/
DECLARE @BEGVND char(21) = 'U07'
SELECT
*
FROM (
SELECT
ROW_NUMBER() OVER (PARTITION BY VENDOR_NUMBER_IVVQ, ITEM_NUMBER_IVVQ
ORDER BY LAST_QUOTE_DATE_IVVQ DESC) AS RN
, ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER_IVVQ
ORDER BY CASE WHEN VENDOR_NUMBER_IVVQ = @BEGVND THEN 1 ELSE 2 END
, VENDOR_NUMBER_IVVQ) AS ORN
, T1.ITEM_NUMBER_IVVQ
, T1.VENDOR_NUMBER_IVVQ
, T1.VENDOR_QUOT_CST_IVVQ
, T1.VENDOR_QUOT_QTY_IVVQ
, T1.LAST_QUOTE_DATE_IVVQ
FROM POWMATQP..IVPLVLVN T1
) X
WHERE X.RN = 1
ORDER BY ITEM_NUMBER_IVVQ, X.ORN
Note I have not used a common table expression as there is no need for that.
;WITH X
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER, VENDOR_NUMBER ORDER BY LAST_QUOTE_DATE DESC) AS RN,ITEM_NUMBER,VENDOR_NUMBER,.VENDOR_QUOT_CST,VENDOR_QUOT_QTY,LAST_QUOTE_DATE
FROM IVPLVLVN)
SELECT *
FROM X
WHERE X.VENDOR_NUMBER=@BEGVND AND X.RN =1
DECLARE @BEGVND CHAR(21)='U07'
;WITH X
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER_IVVQ,VENDOR_NUMBER_IVVQ ORDER BY LAST_QUOTE_DATE_IVVQ DESC) AS RN,T1.ITEM_NUMBER_IVVQ,T1.VENDOR_NUMBER_IVVQ,T1.VENDOR_QUOT_CST_IVVQ,T1.VENDOR_QUOT_QTY_IVVQ,T1.LAST_QUOTE_DATE_IVVQ
FROM POWMATQP..IVPLVLVN T1)
SELECT *
FROM X
WHERE X.RN =1
order by ITEM_NUMBER_IVVQ -- and/or whatever else
If you have multiple instances of multiple groups from T1, then you might want to select distinct (with whatever where clauses) as an initial CTE and then do the row_number() cte, and then the final select DECLARE @BEGVND CHAR(21)='P01'
;WITH X
AS
( SELECT ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER_ivvq,VENDOR_NUMBER_ivvq ORDER BY LAST_QUOTE_DATE_ivvq DESC) AS RN,T1.ITEM_NUMBER_ivvq,T1.VENDOR_NUMBER_ivvq,T1.VENDOR_QUOT_CST_ivvq,T1.VENDOR_QUOT_QTY_ivvq,T1.LAST_QUOTE_DATE_ivvq
,CASE when VENDOR_NUMBER_ivvq = @BEGVND then 1 else 2 end as VSORT
FROM POWMATQP..IVPLVLVN T1
) SELECT *
FROM X
WHERE RN = 1 and (VSORT = 1 or (VSORT = 2 and exists (select null from x where vsort = 1)))
order by ITEM_NUMBER_IVVQ -- and/or whatever else
Seems to work
+----+------------------+--------------------+----------------------+----------------------+----------------------+----------------------+-----+----+
| | ITEM_NUMBER_IVVQ | VENDOR_NUMBER_IVVQ | VENDOR_QUOT_CST_IVVQ | VENDOR_QUOT_QTY_IVVQ | LAST_QUOTE_DATE_IVVQ | FIELD6 | cnt | rn |
+----+------------------+--------------------+----------------------+----------------------+----------------------+----------------------+-----+----+
| 1 | 279A2352P001 | U07 | 295,0 | 12 | 24.02.2017 00:00:00 | KEEP-MOST RECENT U70 | 41 | 1 |
| 2 | 279A2352P001 | C35 | 315,0 | 2 | 19.04.2005 00:00:00 | KEEP-MOST RECENT C35 | 41 | 1 |
| 3 | 279A2352P001 | E03 | 0,0 | 6 | 16.02.2017 00:00:00 | KEEP-MOST RECENT E03 | 41 | 1 |
| 4 | 279A2352P001 | I24 | 0,0 | 5 | 19.05.2003 00:00:00 | KEEP-MOST RECENT I24 | 41 | 1 |
| 5 | 279A2352P001 | M70 | 0,0 | 16 | 21.11.1994 00:00:00 | KEEP-MOST RECENT M70 | 41 | 1 |
+----+------------------+--------------------+----------------------+----------------------+----------------------+----------------------+-----+----+
DECLARE @BEGVND CHAR(21)='U07'
;WITH X
AS
( SELECT ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER_ivvq,VENDOR_NUMBER_ivvq ORDER BY LAST_QUOTE_DATE_ivvq DESC) AS RN,T1.ITEM_NUMBER_ivvq,T1.VENDOR_NUMBER_ivvq,T1.VENDOR_QUOT_CST_ivvq,T1.VENDOR_QUOT_QTY_ivvq,T1.LAST_QUOTE_DATE_ivvq
,CASE when VENDOR_NUMBER_ivvq = @BEGVND then 1 else 2 end as VSORT
FROM POWMATQP..IVPLVLVN T1
) SELECT *
FROM X T1
WHERE RN = 1 and (VSORT = 1 or (VSORT = 2 and exists (select null from x T2 where T2.vsort = 1 and t2.item_number_ivvq = T1.item_number_ivvq)))
I think you will find it performs well with volume because the EXISTS is a very, very quick test compared to having to generating another partitioned sort ( or windoowed functions).