August 10, 2013 at 7:45 pm
I have two tables with the following columns:
Table 1
customer_id
1
2
3
4
5
6
Table 2
id, customer_id, product, date
1, 1, A, 1/1/2013
2, 1, A, 5/1/2013
3, 1, A, 6/1/2013
4, 1, C, 6/1/2013
5, 2, B, 7/1/2013
6, 3, C, 8/1/2013
7, 3, A, 9/1/2013
What I need is the following:
I need to find the maximum date for each customer from Table 2. If there is a tie for the maximum date (2 or more), I need to display all the records that are tied. Also if there is more than one record I need to add an instance field to the results that will count up the number of instances (1, 2, 3, etc). If there is only one returned result from Table 2 for the customer, the instance would default to 1.
Results table
customer_id, date, product, instance
1, 6/1/2013, A, 1
1, 6/1/2013, C, 2
2, 7/1/2013, B, 1
3, 8/1/2013, A, 1
I hope this is clear. I cannot do the max function it appears because it only shows one record and I cannot figure out how to get the instance column.
Thank you in advance and let me know if I can help to clear anything up that may not be clear here.
August 10, 2013 at 8:29 pm
I took your example data and put it into a CTE format so it can be queried. You will find that on this forum board people are more likely to help right away if you put your raw data example in such a form or put it into a CREATE TABLE format as a temp table.
The key to finding the rows you want it to use the MAX() function in the WHERE clause as a subquery to limit the rows returned by the main query. The key to generating the "instance" column is to use the ROW_NUMBER() function partitioned by the customer_id and ordered by something such as the product id.
Here is my solution, including the CTE for the raw data:
with
raw_data (id, customer_id, product, date) as
(select 1, 1, 'A', convert(datetime, '1/1/2013') union all
select 2, 1, 'A', '5/1/2013' union all
select 3, 1, 'A', '6/1/2013' union all
select 4, 1, 'C', '6/1/2013' union all
select 5, 2, 'B', '7/1/2013' union all
select 6, 3, 'C', '8/1/2013' union all
select 7, 3, 'A', '9/1/2013')
select
customer_id,
date,
product,
row_number() over (partition by customer_id order by product) as instance
from
raw_data
where
date = (select max(date) from raw_data r where raw_data.customer_id = r.customer_id)
August 10, 2013 at 8:38 pm
not that familiar with CTEs. I was able to make this work and was able to follow it, but could it be done without a CTE? For example, just joining the tables? Thank you for such a quick reply and the advice on how to post these types of questions. It makes sense so those helping could have the table structure quickly.
Or if I use a CTE, where would I just reference the tables since the data is already there? Can I use a CTE in a view?
Thanks again!
Bob
August 10, 2013 at 8:50 pm
Of course you can point directly at your tables without the CTE. Just drop the top part and use the query at the bottom, with your table name in place of "raw_data," the name of my CTE.
In my own SQL coding I use CTEs as views of data a lot, especially in place of a subquery directly in the FROM clause. They help me break complex query problems down into manageable logical steps because you can have more than one CTE in any given WITH clause, and later ones can reference earlier ones. I highly recommend learning about them and using them liberally.
August 12, 2013 at 6:25 pm
okay....had a chance to get back to this....sorry about the delay.
in the from clause there are 3 table joins. I see that you use raw_data and then reference this again in the where clause. How can I reference this when there are multiple table joins. Is there a way to alias this? Not sure if that makes sense.
Let me know if I can clarify this.
Thanks,
Bob
August 12, 2013 at 6:36 pm
I'm not sure about your question on the joins but using the sample data provided by Geoff, here's my solution (with the "instance" column):
with
raw_data (id, customer_id, product, [date]) as
(select 1, 1, 'A', convert(datetime, '1/1/2013') union all
select 2, 1, 'A', '5/1/2013' union all
select 3, 1, 'A', '6/1/2013' union all
select 4, 1, 'C', '6/1/2013' union all
select 5, 2, 'B', '7/1/2013' union all
select 6, 3, 'C', '8/1/2013' union all
select 7, 3, 'A', '9/1/2013')
SELECT id, customer_id, product, [date]
,instance=COUNT(*) OVER (PARTITION BY customer_id)
FROM (
SELECT id, customer_id, product, [date]
--,instance=COUNT(*)
,rn=DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY [date] DESC)
FROM raw_data) a
WHERE rn=1
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 12, 2013 at 6:37 pm
nevermind.....i got it!!! just put the query in the WITH, aliased that. then followed through the example.
thank you soo much!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply