Need field for each unique set of customer_id, product_id, and max table_id

Question

I have a table that has about seven columns, including a field that contains XML.

There are multiple customer IDs, each of which may have more than one product ID.

For each unique combination of customer ID and product ID, there is more than one table ID (called edw_policy_data_id).

I need to get the XML that corresponds to the max edw_policy_data_id for each unique combination of customer ID and product instance ID.

Finally, I need to restrict the inner query on a date range.

My current query looks like this; I'm returning more columns than I need right now, and ordering them, to debug the query. I haven't been able to do anything meaningful with max() yet.

select edw_policy_data_id, e.customer_id, e.product_instance_id, policy_data_xml
    FROM [DB].[dbo].[EDW_POLICY_DATA] e
inner join (SELECT distinct customer_id, product_instance_id
    FROM [DB].[dbo].[EDW_POLICY_DATA]
    where created_date > '12/1/2011'
    and created_date < '12/27/2011 17:16:00') d
    on e.customer_id = d.customer_id and e.product_instance_id = d.product_instance_id
order by customer_id, product_instance_id, edw_policy_data_id

The results look something like this:

edw_policy_data_id     e.customer_id    e.product_instance_id   policy_data_xml
1                      100              200                     xml
2                      100              200                     xml
3                      100              201                     xml
4                      101              203                     xml
5                      101              203                     xml

I need the results to look like this for debug - note that only the max edw_policy_data_id for each unique customer_ID/product_instance_id combination is present.

edw_policy_data_id     e.customer_id    e.product_instance_id   policy_data_xml
2                      100              200                     xml
3                      100              201                     xml
5                      101              203                     xml

Ultimately, all I want is the XML for each max(edw_policy_id)/customer_id/product_instance_ID combo.

I looked at the responses to this question: no joy.

use the windowing features as seen in my answer, no reason to join or group or MAX() then — cairnz, Dec 28 '11 at 15:04
However if you want to do that, the short-version is select tbl.a,tbl.b, tbl.c from tbl inner join (select a,b,max(c) as c from tbl where ...) as tbl2 on tbl.a = tbl2.a and tbl.b = tbl2.b and tbl.c = tbl2.c - using this you get the full content of col a b c for the table having the max value of c (edw_policy_data_id). — cairnz, Dec 28 '11 at 21:40

score 1 · Answer 1 · answered Dec 28 '11 at 14:44

Try:

select edw_policy_data_id, customer_id, product_instance_id, policy_data_xml
FROM [DB].[dbo].[EDW_POLICY_DATA]
where edw_policy_data_id in
(SELECT max(edw_policy_data_id) 
 FROM [DB].[dbo].[EDW_POLICY_DATA]
 where created_date > '12/1/2011' and created_date < '12/27/2011 17:16:00'
 group by customer_id, product_instance_id) d

ean5533 · Answer 2 · 2011-12-28T14:37:05.800

Here is a modified version of your query that should give you what you need:

select MAX(edw_policy_data_id), e.customer_id, e.product_instance_id, policy_data_xml
    FROM [DB].[dbo].[EDW_POLICY_DATA] e
inner join (SELECT distinct customer_id, product_instance_id
    FROM [DB].[dbo].[EDW_POLICY_DATA]
    where created_date > '12/1/2011'
    and created_date < '12/27/2011 17:16:00') d
    on e.customer_id = d.customer_id and e.product_instance_id = d.product_instance_id
GROUP BY customer_id, product_instance_id, policy_data_xml
order by customer_id, product_instance_id, MAX(edw_policy_data_id)

Note the pieces that were changed:

Get the largest edw_policy_data_id: select MAX(edw_policy_data_id), ...
Group the results by distinct combos of customer_id, product_instance_id, and policy_data_xml: GROUP BY customer_id, product_instance_id, policy_data_xml
ORDER BY needed to be changed as well to accommodate the changes: ORDER BY customer_id, product_instance_id, MAX(edw_policy_data_id)

score 0 · Answer 3 · answered Dec 28 '11 at 14:59

also cte version possible (given that you have a tsql tag)

 DECLARE @create_from DATETIME = '12/1/2011 00:00:00' ;
 DECLARE @create_to DATETIME = GETDATE() ;


 WITH   cte
          AS (
               SELECT
                row_number() OVER ( PARTITION BY product_instance_id ,
                                    customer_id ORDER BY edw_policy_data_id DESC ) AS rn ,
                edw_policy_data_id ,
                e.customer_id ,
                e.product_instance_id ,
                policy_data_xml
               FROM
                [DB].[dbo].[EDW_POLICY_DATA] e
               INNER JOIN [DB].[dbo].[EDW_POLICY_DATA] d
                ON e.customer_id = d.customer_id
                   AND e.product_instance_id = d.product_instance_id
               WHERE
                d.created_date > @create_from
                AND d.created_date < @create_to
             )
      SELECT
        *
      FROM
        cte
      WHERE
        rn = 1

-- Version without any joins:

 DECLARE @create_from DATETIME = '12/1/2011 00:00:00' ;
 DECLARE @create_to DATETIME = GETDATE() ;

 WITH   cte
          AS (
               SELECT
                row_number() OVER ( PARTITION BY product_instance_id ,
                                    customer_id ORDER BY edw_policy_data_id DESC ) AS rn ,
                edw_policy_data_id ,
                customer_id ,
                product_instance_id ,
                policy_data_xml
               FROM
                [DB].[dbo].[EDW_POLICY_DATA]
               WHERE
                created_date BETWEEN @create_from AND @create_to
             )
      SELECT
        *
      FROM
        cte
      WHERE
        rn = 1

seeing this now it looks strange, why do we join onto itself ? edit coming up — cairnz, Dec 28 '11 at 15:00

Need field for each unique set of customer_id, product_id, and max table_id

3 Answers3