1

Let the a table be:

  Product ID    |   Date of Sale  | Time of Sale  |   Buyer ID  | ... 
------------------------------------------------------------------
       1        |    2019-01-09   |     22:21     |     12      | ... 
       1        |    2019-01-09   |     9:44      |     13      | ... 
       1        |    2019-01-03   |     10:32     |     11      | ... 
       2        |    2019-01-08   |     14:51     |     55      | ... 
       2        |    2019-01-02   |     11:20     |     10      | ... 
       .        |        .        |       .       |      .      | ... 
       .        |        .        |       .       |      .      | ... 
       .        |        .        |       .       |      .      | ...

I would like to create a View that displays the row of last product bought. That being said, it would be this table:

  Product ID    |   Date of Sale  | Time of Sale  |   Buyer ID  | ... 
------------------------------------------------------------------
       1        |    2019-01-09   |     22:21     |     12      | ... 
       2        |    2019-01-08   |     14:51     |     55      | ... 
       .        |        .        |       .       |      .      | ... 
       .        |        .        |       .       |      .      | ... 
       .        |        .        |       .       |      .      | ...

I would gladly give my attempts of code but they don't add anything to this question, as they are quite far from an actual solution. However, I've searched extensively on the internet for a couple of minutes. I know that it has to be done with a function.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Chronus
  • 365
  • 3
  • 10
  • Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) – Clockwork-Muse Nov 27 '19 at 23:30

1 Answers1

2

One method is row_number():

select t.*
from (select t.*,
             row_number() over (partition by product_id order by date_of_sale desc, time_of_sale desc) as seqnum
      from t
     ) t
where seqnum = 1;

Usually in this situation, I recommend a correlated subquery. However, you have the date/time split between two columns, so the logic is a bit cumbersome. It makes more sense to just use a datetime data type for the column.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786