How to compare subgroup of rows in SQL

Question

I have ITEMS table below where ITEM_ID column is unique:

ITEM_ID | ITEM_NAME | START_VALUE
  A1324 | item1     | 113
  B4534 | item3     | 50
  A3453 | item7     | 35
  A7654 | item2     | 48

And BIDS table below which contains no unique values:

ITEM_ID | BIDDER_NAME | BID_VALUE | BID_TIME
  A7654 | Jessica     | 53        | 2016-02-02 14:28:43
  B4534 | John        | 69        | 2016-01-03 08:03:06
  B4534 | Annie       | 63        | 2016-01-02 15:23:27
  B4534 | Tim         | 65        | 2016-01-01 18:15:02
  B4534 | AuctionEnd  | 50        | 2016-01-02 19:45:34
  A7654 | Mark        | 52        | 2016-02-02 16:51:12  

I need to join two tables and insert into result table which has the same row count as ITEMS table and also contains highest bidder name and highest bid which is made before AuctionEnd row date. There might be no AuctionEnd row for every item in this case highest bid and bidder should be listed. And if there is no bid at all for particular item this columns should be null. For above tables the result table should look like:

 ITEM_ID|ITEM_NAME|START_VALUE|MAX_BIDDER_NAME|MAX_BID_VALUE|BID_TIME
  A1324 | item1   | 113       |null           |null         |0
  B4534 | item3   | 50        |Tim            |65           |2016-01-01 18:15:02
  A3453 | item7   | 35        |null           |null         |0
  A7654 | item2   | 48        |Jessica        |53           |2016-02-02 14:28:43

This should run on DB2v10 but any other SQL is also welcome. These tables contain millions of rows so performance is main concern here. What should my approach be ?

Answer 1

Another and possibly quicker query as below. Also, make sure you have an index on ITEM_ID on both tables.

YES ... you are correct that I left out logic to allow for bids > the last bid time. The code below shows my complete work. The trick was to first create a cte which had all the VALID BIDS before evaluating for the max bid.

  CREATE TABLE #ITEMS (ITEM_ID varchar(10), ITEM_NAME varchar(10), START_VALUE int)
  INSERT INTO #ITEMS
  VALUES
  ('A1324','item1',113),
  ('B4534','item3',50),
  ('A3453','item7',35),
  ('A7654','item2',48)


  CREATE TABLE #BIDS (ITEM_ID varchar(10), BIDDER_NAME varchar(50), BID_VALUE int, BID_TIME datetime)
  INSERT INTO #BIDS
  VALUES
  ('A7654','Jessica',53,'2016-02-02 14:28:43'),
  ('B4534','John',69,'2016-01-03 08:03:06'),
  ('B4534','Annie',63,'2016-01-02 15:23:27'),
  ('B4534','Tim',65,'2016-01-01 18:15:02'),
  ('B4534','AuctionEnd',50,'2016-01-02 19:45:34'),
  ('A7654','Mark',52,'2016-02-02 16:51:12')


;WITH cteEndTime as
(
    Select 
        ITEM_ID,
        CASE WHEN BIDDER_NAME = 'AuctionEnd' THEN BID_TIME ELSE NULL END as 'END_BID_TIME'
    From #BIDS
    Where BIDDER_NAME = 'AuctionEnd'
)

,cteValidBIDS as
(Select * From (
    Select bd.ITEM_ID, bd.BIDDER_NAME, bd.BID_VALUE, bd.BID_TIME,
        ROW_NUMBER() over (partition by bd.ITEM_ID order by BID_TIME) as 'brnk',
        c.END_BID_TIME
    FROM #BIDS bd
    LEFT JOIN cteEndTime c on c.ITEM_ID = bd.ITEM_ID
) a
Where a.BID_TIME < ISNULL(a.END_BID_TIME, '2200-01-01')
)

,finalCTE as (
Select ITEM_ID, MAX_BID_NAME, MAX_BID_VALUE, BID_TIME From (
    Select
        ROW_NUMBER() over (partition by c.ITEM_ID order by c.BID_VALUE desc) as 'brnk'
        ,c.ITEM_ID
        ,c.BIDDER_NAME as 'MAX_BID_NAME'
        ,c.BID_VALUE as 'MAX_BID_VALUE'
        ,c.BID_TIME
    From cteValidBIDS c
    ) a
    Where a.brnk = 1
)

Select i.ITEM_ID, i.ITEM_NAME, i.START_VALUE, c.MAX_BID_NAME, c.MAX_BID_VALUE, c.BID_TIME
From #ITEMS i
LEFT JOIN finalCTE c on c.ITEM_ID = i.ITEM_ID