T-SQL OVER/PARTITION BY query with condition

Question

I am struggling with a SQL query and while I have looked at many similar answers none of them quite fit my situation. I have a dataset as below:

Date1       Amount 1    Index   Date2               Type Supplier
31/03/2018  410000.00   17      16/04/2018 06:27    102  A
31/03/2018  410000.00   17      16/04/2018 06:31    102  B
31/03/2018  400000.00   2       16/04/2018 06:37    102  A
31/03/2018  400000.00   2       16/04/2018 06:38    102  B
30/06/2018  0           20      04/07/2018 08:23    202  A
30/06/2018  0           20      04/07/2018 08:23    202  B
30/06/2018  412000.00   20      06/07/2018 12:46    102  A
30/06/2018  412000.00   20      06/07/2018 12:47    102  B
30/06/2018  442000.00   100     16/07/2018 06:27    102  A
30/06/2018  442000.00   100     16/07/2018 06:31    102  B

For each Date1 where there are multiple rows with the same Type, I only want the rows where the index matches the index of the maximum Date2 so I want this output:

Date1       Amount 1    Index   Date2               Type Supplier
31/03/2018  400000.00   2       16/04/2018 06:37    102  A
31/03/2018  400000.00   2       16/04/2018 06:38    102  B
30/06/2018  0           20      04/07/2018 08:23    202  A
30/06/2018  0           20      04/07/2018 08:23    202  B
30/06/2018  442000.00   100     16/07/2018 06:27    102  A
30/06/2018  442000.00   100     16/07/2018 06:31    102  B

I feel it should be possible with some form of conditional MAX() OVER (PARTITION BY) but for the life of me I can't work out how to do it.

Answer 1

Use LAST_VALUE (Transact-SQL) analytic function together with a subquery.

The below working example is for Oracle (I prefer Oracle because I always have a problem with converting dates on SQLServer), but the idea of the query is the same, the syntax also is the same:

Demo: http://www.sqlfiddle.com/#!4/004ce7/19

SELECT * FROM (
   SELECT t.* ,
     last_value( "INDEX" ) OVER 
        ( partition by date1, "TYPE" order by date2
          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) xx
   FROM table1 t
) x
WHERE xx = "INDEX"
ORDER BY date1;

---

|                DATE1 | AMOUNT1 | INDEX |                 DATE2 | TYPE | SUPPLIER |  XX |
|----------------------|---------|-------|-----------------------|------|----------|-----|
| 2018-03-31T00:00:00Z |  400000 |     2 | 2018-04-16 06:37:00.0 |  102 |        A |   2 |
| 2018-03-31T00:00:00Z |  400000 |     2 | 2018-04-16 06:38:00.0 |  102 |        B |   2 |
| 2018-06-30T00:00:00Z |  442000 |   100 | 2018-07-16 06:27:00.0 |  102 |        A | 100 |
| 2018-06-30T00:00:00Z |  442000 |   100 | 2018-07-16 06:31:00.0 |  102 |        B | 100 |
| 2018-06-30T00:00:00Z |       0 |    20 | 2018-07-04 08:23:00.0 |  202 |        B |  20 |
| 2018-06-30T00:00:00Z |       0 |    20 | 2018-07-04 08:23:00.0 |  202 |        A |  20 |