Selecting multiple records based maximum dates using Row_Number().

dont you have any INPUT DATE, or DATA ENTRY DATE in your tables?, that would be a lot better than trying to use rownum

Seems like you should be able to add VENDOR_NUMBER to the partitioning:

(SELECT ROW_NUMBER() OVER (PARTITION BY VENDOR_NUMBER, ITEM_NUMBER ORDER BY ITEM_NUMBER,LAST_QUOTE_DATE DESC) AS RN,ITEM_NUMBER,VENDOR_NUMBER,VENDOR_QUOT_CST,VENDOR_QUOT_QTY,LAST_QUOTE_DATE
FROM IVPLVLVN)

I have tried several variations without success. This script will be embedded in an SSRS report. 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'

;WITH X
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY VENDOR_NUMBER_IVVQ,ITEM_NUMBER_IVVQ ORDER BY VENDOR_NUMBER_IVVQ,ITEM_NUMBER_IVVQ,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.VENDOR_NUMBER_IVVQ=@BEGVND and X.ITEM_NUMBER_IVVQ<>'' AND X.RN =1) OR (X.VENDOR_NUMBER_IVVQ<>@BEGVND and X.ITEM_NUMBER_IVVQ<>'' AND X.RN >1)

I believe you need more than a single row number calculation. One for the filtering, and another for the ordering. For filtering just concentrate on getting the most recent rows regardless of vendor.

For ordering the result rows partition by item number then in order by using a case expression so that if the vendor matches the parameter set that to 1, else 2. Then fine-tune the remaining order by the vendor.

Filtering by rn=1 should be all you now require, then use the second calculation to assist ordering of the result

/*
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

Select allOpen in new window

Note I have not used a common table expression as there is no need for that.

Did you try Scott's suggestion? That should solve your need based on your sample data and expected result.
It doesn't harm but you can remove ITEM_NUMBER in the ORDER BY clause.

;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

Select allOpen in new window

Maybe I am not understanding, but seems fairly straightforward.

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 

Select allOpen in new window

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

If you want to see the seected vendor at the top of the list, then you could do something like : CASE when VENDOR_NUMBER_IVVQ = @BEGVND then 1 else 2 end as VSORT and use that in the Order By. Can also check VSORT in the final WHERE clause : WHERE X.RN =1 and VSORT = 1  -- etc
or if wanting other venfors but only if the parameter one exists :    where RN = 1 and (VSORT = 1 or (VSORT = 2 and exists (select null from x where vsort = 1)))

Does that make sense ?

OK, we are real close. Scott's version only gives me records for the specified vendor and PortletPaul's version gives me all items.  If the item does not come from the specified vendor I want no records to be returned. If the item does come from the specified vendor, I want the most recent record for that vendor and then the most recent record for all other vendors that provide that part. You have all been most helpful. I will tweak this some more in the morning.

Can you provide some sample data and expected result for your last post?

I discovered that many rows have blanks for the item and/or vendor number. My revised script looks like this now.

SELECT
    *
FROM (
    SELECT
          ROW_NUMBER() OVER (PARTITION BY T1.ITEM_NUMBER_IVVQ, T1.VENDOR_NUMBER_IVVQ
                             ORDER BY LAST_QUOTE_DATE_IVVQ DESC) AS RN
        , ROW_NUMBER() OVER (PARTITION BY T1.ITEM_NUMBER_IVVQ
                             ORDER BY CASE WHEN VENDOR_NUMBER_IVVQ = @BEGVND THEN 1 ELSE 2 END
                                     , VENDOR_NUMBER_IVVQ) AS ORN
        , CASE WHEN T1.ITEM_NUMBER_IVVQ <> '' THEN T1.ITEM_NUMBER_IVVQ ELSE '999Z9999P999' END AS ITEM_NUMBER_IVVQ
        , CASE WHEN T1.VENDOR_NUMBER_IVVQ <>'' THEN T1.VENDOR_NUMBER_IVVQ ELSE 'Z99' END AS VENDOR_NUMBER_IVVQ
        , T1.VENDOR_QUOT_CST_IVVQ
        , T1.VENDOR_QUOT_QTY_IVVQ
        , T1.LAST_QUOTE_DATE_IVVQ
    FROM POWMATQP..IVPLVLVN T1
      WHERE T1.ITEM_NUMBER_IVVQ <> '' AND T1.VENDOR_NUMBER_IVVQ <>''
) X
WHERE (X.RN = 1 AND VENDOR_NUMBER_IVVQ=@BEGVND) OR (X.RN>1 AND X.ORN > 1 AND VENDOR_NUMBER_IVVQ<>@BEGVND)
ORDER BY ITEM_NUMBER_IVVQ, VENDOR_NUMBER_IVVQ,X.RN, X.ORN

Just loaded your spreadsheet  (@sharath see SampleQuoteCost.xlsx in question header - carefull - column names need _IVVQ appended)

and ran the following code as per my previous post

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 

Select allOpen in new window

Seems to work

Here is a more complete dataset. The 'DoNotWant' illustrate records to not be included. Since none of them come from U70 I want none of them. The 'WantThese' tabs shows the ones I do want. U70 for one and then the most recent of each vendor, as marked.
ItemsByVendor.xlsx

This version is very close. However, look at the file sent with this. Part 199D3980G001 should not be included as it does not come from U07.

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,CASE when VENDOR_NUMBER_ivvq = @BEGVND then 1 else 2 end as VSORT,
  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
        WHERE T1.ITEM_NUMBER_IVVQ <> '' AND T1.VENDOR_NUMBER_IVVQ <>''
) 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,VENDOR_NUMBER_IVVQ -- and/or whatever else
No199D3980G001.png

I need to call it a night. My apologies. I will see your comments in the morning.

nice work Sharath!
for ordering

ORDER BY ITEM_NUMBER_IVVQ, CASE WHEN  VENDOR_NUMBER_IVVQ = @BEGVND THEN 1 ELSE 2 END

+----+------------------+--------------------+----------------------+----------------------+----------------------+----------------------+-----+----+
|    | 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  |
+----+------------------+--------------------+----------------------+----------------------+----------------------+----------------------+-----+----+

Select allOpen in new window

@rwheeler
 
Simple, just add the item_number_ivvq test to the EXISTS e.g.

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)))

Select allOpen in new window

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).

Thank you all for your help. Your gifted insight is always very much appreciated.