SQL Query to get 2nd most recent results with multiple columns

I am trying to obtain the 2nd most recent results for all distinct part_id’s(based off of order_date) to go into a report I am working on making to compare it to the most recent results.

The commented sections are from another approach I was trying but unsuccessful with.

Any help is greatly appreciated!

(Side note: I am new to posting on SO, and I apologize in advance if this is answered elsewhere, but I was unable to find anything that pertained to this issue)

I am using the following query:

SELECT
                              PURCHASE_ORDER.ORDER_DATE
                            , PURC_ORDER_LINE.PART_ID
                            , PURCHASE_ORDER.VENDOR_ID
                            , PURC_ORDER_LINE.LINE_STATUS
                            , PURC_ORDER_LINE.ORDER_QTY
                            , PURC_ORDER_LINE.UNIT_PRICE
                            --, ROW_NUMBER() over (ORDER BY PURCHASE_ORDER.ORDER_DATE DESC)AS ROW
                            , CAST (PURC_ORDER_LINE.ORDER_QTY * PURC_ORDER_LINE.UNIT_PRICE AS VARCHAR) AS TOTAL_COST
                    FROM
                              PURCHASE_ORDER
                              INNER JOIN
                                        PURC_ORDER_LINE
                              ON
                                        PURCHASE_ORDER.ID = PURC_ORDER_LINE.PURC_ORDER_ID

                    WHERE PURCHASE_ORDER.ORDER_DATE < (SELECT MAX(ORDER_DATE) FROM PURCHASE_ORDER) AND PURC_ORDER_LINE.PART_ID = 'XXXX'
                    ORDER BY ORDER_dATE DESC





                    --WHERE PURC_ORDER_LINE.PART_ID = 'XXXX' and PURCHASE_ORDER.ORDER_DATE = (SELECT MAX(ORDER_DATE) FROM PURCHASE_ORDER WHERE ORDER_DATE < (SELECT MAX(ORDER_DATE) FROM PURCHASE_ORDER))

EDIT 5/28 LATE NIGHT:

Lets say below is the data set I need the 2nd result from each part_id (2nd based off of ORDER_DATE DESC)

+-------------+---------+-----------+
| ORDER_DATE  | PART_ID | VENDOR_ID |
+-------------+---------+-----------+
| 2020-05-29  | XXXX    | CVVB      |
| 2020-05-27  | XXXX    | CVVB      |
| 2020-05-28  | XXXX    | CVVA      |
| 2020-05-28  | YYYY    | GGNB      |
| 2020-04-12  | YYYY    | GGNB      |
| 2020-02-08  | YYYY    | GGNB      |
| 2020-05-28  | ZZZZ    | LLNB      |
| 2019-10-28  | ZZZZ    | LLNB      |
| 2019-05-27  | ZZZZ    | OKIJ      |
+-------------+---------+-----------+

I am looking to receive the following output (for more than 3 different part id’s):

+------------+---------+-----------+
| ORDER_DATE | PART_ID | VENDOR_ID |
+------------+---------+-----------+
| 2020-05-28 | XXXX    | CVVA      |
| 2020-04-12 | YYYY    | GGNB      |
| 2019-10-28 | ZZZZ    | LLNB      |
+------------+---------+-----------+

There are also additional columns in the query but formatting them as a table would have taken much longer. I have left off a few of the columns on the examples.

ANOTHER EDIT

I am not sure if this information helps, but I am trying to compare the most recent results to the previous results to show pricing and vendor differences. We are compiling the data into Report Builder; My approach here was to create 2 separate datasets one with the most recent and the other with the 2nd most recent and combine the data from the datasets in Report Builder. If there is an easier approach and I am heading in the wrong direction please let me know!

Example:

+------------+---------+-----------+-------------+----------+------------+
| ORDER_DATE | PART_ID | VENDOR_ID | Porder_Date | Ppart_ID | pVendor_id |
+------------+---------+-----------+-------------+----------+------------+
| 2020-05-29 | XXXX    | CVVB      | 2020-05-28  | XXXX     | CVVA       |
| 2020-05-28 | YYYY    | GGNB      | 2020-04-12  | YYYY     | GGNB       |
| 2020-05-28 | ZZZZ    | LLNB      | 2019-10-28  | ZZZZ     | LLNB       |
+------------+---------+-----------+-------------+----------+------------+

EDIT THE NEXT MORNING

Thanks everyone for all the help! After Harry posted his solution I went ahead and made some tiny edits to get the columns I needed added on. I swapped his union portion with the original select statement. Everything here seems to be exactly what I am looking for!

Code:

;
WITH mycte AS
          (
                    SELECT
                              PURCHASE_ORDER.ORDER_DATE
                            , PURC_ORDER_LINE.PART_ID
                            , PURCHASE_ORDER.VENDOR_ID
                            , PURC_ORDER_LINE.LINE_STATUS
                            , PURC_ORDER_LINE.ORDER_QTY
                            , PURC_ORDER_LINE.UNIT_PRICE
                            , CAST (PURC_ORDER_LINE.ORDER_QTY * PURC_ORDER_LINE.UNIT_PRICE AS VARCHAR) AS TOTAL_COST
                    FROM
                              PURCHASE_ORDER
                              INNER JOIN
                                        PURC_ORDER_LINE
                              ON
                                        PURCHASE_ORDER.ID = PURC_ORDER_LINE.PURC_ORDER_ID
          )
        , mycte2 AS
          (
                    SELECT
                              CONVERT(DATE,order_date) AS order_date
                            , part_id
                            , vendor_id
                            , order_qty
                            , unit_price
                            , total_cost
                            , ROW_NUMBER() over(
                                      PARTITION BY part_id
                                      ORDER BY
                                                CONVERT(DATE,order_date) DESC) AS row_num
                    FROM
                              mycte
          )
SELECT
          mycte2.order_date
        , mycte2.part_id
        , mycte2.vendor_id
        , mycte2.order_qty
        , mycte2.unit_price
        , mycte2.total_cost
        , previous.order_date porder_date
        , previous.part_id    ppart_id
        , previous.vendor_id  pvendor_id
        , previous.order_qty  poqrder_qty
        , previous.unit_price punit_price
        , previous.total_cost ptotal_cost
FROM
          mycte2
          LEFT JOIN
                    mycte2 previous
          ON
                    previous.row_num   = mycte2.row_num +1
                    AND mycte2.part_id = previous.part_id
WHERE
          mycte2.row_num = 1

Answer

Based on the data you have provided, you can do this with a cte and row number function.

Note – it always helps to show the whole picture rather than just ask for the part you want help with (usually).. as it is easier to answer if we can understand the entire issue!

See code below

;with mycte as (


select 
 '2020-05-29' as order_date  , 'XXXX'  as part_id   , 'CVVB' as vendor_id
 union all select
 '2020-05-27'  , 'XXXX'    , 'CVVB'  
  union all select
 '2020-05-28'  , 'XXXX'    , 'CVVA'      
  union all select
 '2020-05-28'  , 'YYYY'    , 'GGNB'     
  union all select
 '2020-04-12'  , 'YYYY'    , 'GGNB'      
  union all select
 '2020-02-08'  , 'YYYY'    , 'GGNB'     
  union all select
 '2020-05-28'  , 'ZZZZ'    , 'LLNB'      
  union all select
 '2019-10-28' , 'ZZZZ'    , 'LLNB'    
  union all select
 '2019-05-27'  , 'ZZZZ'    , 'OKIJ' 
 )
 , mycte2 as (
 Select 
  convert(date,order_date) as order_date
 ,part_id
 ,vendor_id
 ,ROW_NUMBER() over( partition by part_id order by convert(date,order_date) desc) as row_num
 from mycte
)

Select 
mycte2.order_date
,mycte2.part_id
,mycte2.vendor_id
,previous.order_date porder_date
,previous.part_id ppart_id
,previous.vendor_id pvendor_id
from  mycte2

 left join mycte2 previous
    on previous.row_num = mycte2.row_num +1
    and mycte2.part_id = previous.part_id

where mycte2.row_num = 1

result