Selecting the first instance of a vendor, part combination

Question

I am trying to create an indicator for if a particular transaction was the first time a part was purchased from a particular vendor.

I have a dataset that looks like this:

| transaction_id | vendor_id | part_id |     trans_date    |
|:--------------:|:---------:|:-------:|:-----------------:|
|    9Bx*2Pc'    |     a     |   873   |     10/12/2018    |
|    1Po.4Ot,    |     a     |   473   |     4/22/2016     |
|    9Sk"7Kv/    |     b     |   123   |     7/23/2016     |
|    2Lz&7Hu&    |     a     |   873   |     12/20/2017    |
|    8Lz)5Is#    |     b     |   743   |     10/22/2016    |
|    5Sc'6Jl/    |     a     |   113   |     10/6/2016     |
|    0Ra&8Hb&    |     a     |   653   |     10/4/2017     |
|    4Wc-8Of*    |     c     |   333   |      8/3/2017     |
|    8Vv+9Yo/    |     c     |   333   |     12/7/2016     |
|    6Qh!1Ha-    |     c     |   333   |     3/28/2017     |
|    2Ol%4Rs#    |     c     |   333   |      5/2/2017     |
|    1Gg#8Cm%    |     c     |   333   |     11/15/2016    |
|    0Lw(6Pv/    |     d     |   873   |     8/13/2017     |
|    1Gy/7Zw,    |     a     |   443   |     10/12/2018    |
|    2Gz,4Gp.    |     b     |   103   |      1/5/2018     |
|    5Dj)6Wc+    |     a     |   893   |     12/17/2016    |
|    5Hl-8Ds!    |     a     |   903   |     12/8/2017     |
|    8Ws$3Vy*    |     b     |   873   |     1/13/2018     |

What I am looking to do is determine if the transaction_id was the first time (sorted by trans_date), that the part_id was purchased from a vendor_id. I would imagine the ideal output to look like this:

| transaction_id | vendor_id | part_id |     trans_date    | first_time |
|:--------------:|:---------:|:-------:|:-----------------:|:----------:|
|    9Bx*2Pc'    |     a     |   873   |     10/12/2018    |      N     |
|    1Po.4Ot,    |     a     |   473   |     4/22/2016     |      Y     |
|    9Sk"7Kv/    |     b     |   123   |     7/23/2016     |      Y     |
|    2Lz&7Hu&    |     a     |   873   |     12/20/2017    |      Y     |
|    8Lz)5Is#    |     b     |   743   |     10/22/2016    |      Y     |
|    5Sc'6Jl/    |     a     |   113   |     10/6/2016     |      Y     |
|    0Ra&8Hb&    |     a     |   653   |     10/4/2017     |      Y     |
|    4Wc-8Of*    |     c     |   333   |      8/3/2017     |      N     |
|    8Vv+9Yo/    |     c     |   333   |     12/7/2016     |      N     |
|    6Qh!1Ha-    |     c     |   333   |     3/28/2017     |      N     |
|    2Ol%4Rs#    |     c     |   333   |      5/2/2017     |      N     |
|    1Gg#8Cm%    |     c     |   333   |     11/15/2016    |      Y     |
|    0Lw(6Pv/    |     d     |   873   |     8/13/2017     |      Y     |
|    1Gy/7Zw,    |     a     |   443   |     10/12/2018    |      Y     |
|    2Gz,4Gp.    |     b     |   103   |      1/5/2018     |      Y     |
|    5Dj)6Wc+    |     a     |   893   |     12/17/2016    |      Y     |
|    5Hl-8Ds!    |     a     |   903   |     12/8/2017     |      Y     |
|    8Ws$3Vy*    |     b     |   873   |     1/13/2018     |      Y     |

So far, I have tried (which was influenced by this post):

WITH
  first_instance AS (
    SELECT
      tbl_trans.*,
      ROW_NUMBER() OVER (PARTITION BY vendor_id||part_id ORDER BY trans_date) AS row_nums
    FROM
      tbl_trans
 )
 
 SELECT
   x.*,
   CASE WHEN y.row_nums = 1 THEN 'Y' ELSE 'N' END AS first_time_indicator
 FROM
   tbl_trans x
     LEFT JOIN first_instance y

But I am met with:

ORA-00905: missing keyword

I have created a SQL FIDDLE with this data and the query thus far for testing. How can I determine the if a transaction was a first time purchase for a part/vendor combination?

Gordon Linoff · Accepted Answer · 2020-11-30T14:48:31.780

1

Use window functions:

select t.*,
       (case when row_number() over (partition by vendor_id, part_id order by trans_date) = 1
             then 'Y' else 'N'
        end) as first_time
from tbl_trans t;

You don't need a join.

edited Nov 30 '20 at 14:48

answered Nov 30 '20 at 14:42

Gordon Linoff

1,242,037
58
646
786

When I run this in my [SQL FIDDLE][http://sqlfiddle.com/#!4/59540/5], I get: `ORA-00907: missing right parenthesis` – artemis Nov 30 '20 at 14:44
Missing ORDER BY before trans_date – gogocho Nov 30 '20 at 14:45

Popeye · Answer 2 · 2020-11-30T17:33:34.470

Apart from row_number, there are multiple ways of achieving the desired result using analytical function as follows.

You can use first_value analytical function as follows:

Select t.*,
       Case 
         when first_value(trans_date) 
                over (partition by vendor_id, part_id order by trans_date) = trans_date
         then 'Y' 
         else 'N' 
       end as first_time
From your_table t;

The same way, you can also use min as follows:

Select t.*,
       Case 
         when min(trans_date) 
                over (partition by vendor_id, part_id) = trans_date
         then 'Y' 
         else 'N' 
       end as first_time
From your_table t;

Selecting the first instance of a vendor, part combination

2 Answers2