February 21, 2012 at 7:47 am
Hi. I'm wondering if someone can help me with this query. I need to obtain the max tableC.surrogateID2 and corresponding tableC.description, based on each tableA.productID, but only including a specific tableC.description_code. I also need to make sure that I am pulling the max tableB.surrogateID1 At the bottom is my attempt at producing this list. My syntax may be bad as I am trying to create an abstracted example. Any ideas? Hopefully you can understand what I have here.
Edited to show actual SQL. I have very limited privileges so hopefully this will parse.....
create table #tableA
(
surrogateID1 int,
productID int
)
GO
insert into #tableA (surrogateID1, productID)
select 500000, 1000
UNION ALL
select 500001, 1001
UNION ALL
select 500002, 1002
UNION ALL
select 500003, 1003
UNION ALL
select 500004, 1004
UNION ALL
select 500005, 1005
GO
create table #tableB
(
surrogateID1 int,
surrogateID2 int,
)
insert into #tableB (surrogateID1, surrogateID2)
select 500000, 900000
UNION ALL
select 500001, 900001
UNION ALL
select 500002, 900002
UNION ALL
select 500003, 900003
UNION ALL
select 500004, 900004
UNION ALL
select 500005, 900005
GO
create table #tableC
(
surrogateID2 int,
description_date datetime,
description_code char(4),
description char(200)
)
insert into #tableC (surrogateID2, description_date, description_code, description)
select 900000, 5/1/2010, 'D01', 'Lorem ipsum dolor sit amet consectetur adipiscing elit'
UNION ALL
select 900001, 5/1/2010, 'D01', 'Lorem ipsum dolor sit amet consectetur adipiscing elit'
UNION ALL
select 900002, 5/20/2010, 'D03', 'Maecenas non metus a velit euismod faucibus'
UNION ALL
select 900003, 5/21/2010, 'D03', 'Maecenas non metus a velit euismod faucibus'
UNION ALL
select 900004, 8/4/2010, 'D04', 'Etiam eleifend orci vel erat scelerisque tempor'
UNION ALL
select 900005, 8/5/2010, 'D05', 'Suspendisse fringilla bibendum risus ut vestibulum'
GO
--Select statement
select
A.productID,
B.surrogateID2,
C.description
from
#tableB as B
INNER JOIN #tableA as A on b.surrogateID1 = a.surrogateID1
INNER JOIN #tableC as C on b.surrogateID2 = c.surrogateID2
where
b.surrogateID1 = (select max(b2.surrogateID1)
from #tableB as b2
where b.surrogateID1 = b2.surrogateID1)
and
c.surrogateID2 = (select max(c2.surrogateID2)
from #tableC as c2
where c.surrogateID2 = c2.surrogateID2
and c2.description_code = 'D03')
order by a.productID
February 21, 2012 at 8:17 am
Take a look at the first link in my signature for best practices on posting questions. Basically you need to post ddl (create table statements), sample data (insert statements) and desired output based on your sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 21, 2012 at 9:22 am
Thanks for the suggestion Sean. See newly edited post above.
JS
February 22, 2012 at 2:17 am
I need to obtain the max tableC.surrogateID2 and corresponding tableC.description, based on each tableA.productID, but only including a specific tableC.description_code. I also need to make sure that I am pulling the max tableB.surrogateID1
I am not sure that your data is clearly representative of what you're trying to do:
- If you want max tableC.surrogateID2, why are you joining on that (second INNER JOIN)?
- If you want max max tableB.surrogateID1, why are you joining on that (first INNER JOIN)?
- By max, do you mean by max description date?
- Should the final recordset only include SurrogateID1s where there's a D03 record in #TableC?
If surrogateIDs are duplicated in any of these tables, I think you should show that in your sample data, otherwise I can't figure out what you want.
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
February 22, 2012 at 7:42 am
As stated above, your requirements are unclear. If you are looking for MAX values, I don't see any duplicates in your data except for descriptions and codes in #TableC. Perhaps more data would help because there is currently and one-to-one relationship between the values now.
In addition, it would be helpful for you to layout the expected results. Just build a sample result set that we can use as our guide when trying to produce a query.
February 24, 2012 at 10:47 am
See updated script above. I had an incorrect alias specified in my max where clauses. If you run the script that i provided, you will get two records. I want only the second record that is returned, which has a greater surrogateID2 value.
thx,
JS
February 24, 2012 at 11:58 am
Like this?
select top 1
A.productID,
B.surrogateID2,
C.description
from
#tableB as B
INNER JOIN #tableA as A on b.surrogateID1 = a.surrogateID1
INNER JOIN #tableC as C on b.surrogateID2 = c.surrogateID2
where
b.surrogateID1 = (select max(b2.surrogateID1)
from #tableB as b2
where b.surrogateID1 = b2.surrogateID1)
and
c.surrogateID2 = (select max(c2.surrogateID2)
from #tableC as c2
where c.surrogateID2 = c2.surrogateID2
and c2.description_code = 'D03')
order by a.productID desc
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 27, 2012 at 12:28 pm
Thanks to all who tried to help me on this one. The final solution ended up using a CTE and a row_number function similar to the code below. It was quite difficult to portray my real world example in an abstracted manner on this thread so I apologize for any confusion.
Thanks all!
JS
;WITH CTE AS (
SELECT
TC.surrogateID2,
TC.description,
ROW_NUMBER() OVER(ORDER BY TC.surrogateID2 DESC) Rnk
FROM
@tableC TC
WHERE
TC.description_code = @dcode
)
SELECT
CTE.surrogateID2,
CTE.description,
TA.productID
FROM
CTE
INNER JOIN @tableB TB
ON TB.surrogateID2 = CTE.surrogateID2
INNER JOIN @tableA TA
ON TA.surrogateID1 = TB.surrogateID1
WHERE
CTE.Rnk = 1
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply