August 7, 2012 at 7:26 am
asm1212 (8/7/2012)
Guys,This has gotten overblown...
When I first posted that sample code stuff and realized it wasnt formatted once I finished posting, I remembered that there was a specific way yall like for people to post to help yall understand better what is being asked...And I said I apologize for posting the sample stuff in that way and that I just didnt have the time to post my whole process b/c I knew it was much larger than I originally had...And I said I would go on, then someone made a post that kind of rubbed me the wrong way and then I responded with my little rant which I know was not the best thing for me to do, but I have been fighting this for awhile and when I read that post, it frustrated me! I do know yall here to help and I understand why yall want folks to post in a certain manner...I just want to be clear that was not my reason for my previous post!
Thank you all who responded with ideas how to get this accomplished...I was looking at how I can use the OVER PARTITION BY clause...
I agree this has gotten to be like dog piling. I am sure that it was my post that rubbed you the wrong way. The tone of my post was because yours rubbed me the wrong way. I apologized for my snarky post and moved on.
Did you figure out how you can your data now?
_______________________________________________________________
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/
August 7, 2012 at 7:34 am
asm1212 (8/7/2012)
Guys,This has gotten overblown...
When I first posted that sample code stuff and realized it wasnt formatted once I finished posting, I remembered that there was a specific way yall like for people to post to help yall understand better what is being asked...And I said I apologize for posting the sample stuff in that way and that I just didnt have the time to post my whole process b/c I knew it was much larger than I originally had...And I said I would go on, then someone made a post that kind of rubbed me the wrong way and then I responded with my little rant which I know was not the best thing for me to do, but I have been fighting this for awhile and when I read that post, it frustrated me! I do know yall here to help and I understand why yall want folks to post in a certain manner...I just want to be clear that was not my reason for my previous post!
Thank you all who responded with ideas how to get this accomplished...I was looking at how I can use the OVER PARTITION BY clause...
It happens. Appologies if I helped create any of the irritation.
Here's a skeleton sample of what I was talking about:
with CTE as
(select Col1, Col2, Col3,
row_number() over (partition by Col1, Col2 order by Col3 desc) as R)
select *
from CTE
where R = 1;
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2012 at 8:20 am
No I am not familiar with CTE's...I have never worked with them!
August 7, 2012 at 8:27 am
asm1212 (8/7/2012)
No I am not familiar with CTE's...I have never worked with them!
Does the sample I posted help?
The documentation is here: http://msdn.microsoft.com/en-us/library/ms175972.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2012 at 8:33 am
asm1212 (8/7/2012)
No I am not familiar with CTE's...I have never worked with them!
Using the incredibly simplified ddl I posted a few posts back here is an example of the view with a CTE.
create view MyInvoiceViewWithCTE
as
with CTE as
(
select InvoiceID, DueDate, GroupID, SubscriberID,
ROW_NUMBER() over (Partition by DueDate, GroupID, SubscriberID Order by InvoiceID) as RowNum
from MyInvoice
)
select * from CTE
where RowNum = 1
go
You could also do this using a subquery. Here is the example.
create view MyInvoiceViewWithSubQuery
as
select * from
(
select InvoiceID, DueDate, GroupID, SubscriberID,
ROW_NUMBER() over (Partition by DueDate, GroupID, SubscriberID Order by InvoiceID) as RowNum
from MyInvoice
) r
where r.RowNum = 1
go
This is really a personal preference approach here. Some people prefer the CTE and other prefer the subquery. I have never seen evidence that one will performance better than the other. Everytime I have looked into this they always produce identical execution plans.
_______________________________________________________________
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/
August 7, 2012 at 8:39 am
Non-recursive CTEs, and CTEs that don't reference prior CTEs, are functionally identical to classic derived tables in the From clause. There's no difference between the two in those cases. So, yes, they produce the same result, same performance, same execution plan, etc. They just look different.
CTEs can do things classic derived tables can't, like recurse, or reference each other sequentially, and they have some readability advantages (top-to-bottom instead of inside-to-out), so I tend to use them over the other. When either will do, I usually use a CTE, for consistency with cases where I have to use one.
When you can use either, it's a personal preference, of course. (Unless you have company coding guidelines that say otherwise, of course.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2012 at 8:47 am
GSquared (8/7/2012)
Non-recursive CTEs, and CTEs that don't reference prior CTEs, are functionally identical to classic derived tables in the From clause. There's no difference between the two in those cases. So, yes, they produce the same result, same performance, same execution plan, etc. They just look different.CTEs can do things classic derived tables can't, like recurse, or reference each other sequentially, and they have some readability advantages (top-to-bottom instead of inside-to-out), so I tend to use them over the other. When either will do, I usually use a CTE, for consistency with cases where I have to use one.
When you can use either, it's a personal preference, of course. (Unless you have company coding guidelines that say otherwise, of course.)
Thanks for clearing up my mumbling Gus. That is what I meant but didn't really know how to make my thoughts coherent. 😛
_______________________________________________________________
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/
August 7, 2012 at 8:50 am
Thanks guys, I tried using this (CTE) and it seems to come back with expected results!
I have go back and check the business rules regarding this to confirm this is what I am looking for!
I apologize once again for any 'ruckus' that was caused!
August 7, 2012 at 8:52 am
Glad we could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2012 at 8:52 am
asm1212 (8/7/2012)
Thanks guys, I tried using this (CTE) and it seems to come back with expected results!I have go back and check the business rules regarding this to confirm this is what I am looking for!
I apologize once again for any 'ruckus' that was caused!
You are quite welcome. Glad we could help and thanks for letting us know. The rest is all water under the bridge. 😀
Feel free to post back here if you run into any more issues.
_______________________________________________________________
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/
Viewing 10 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply