trying to understand a solution from markros

[wbodger]

the code given was

;with cte as (select *, row_number() over (partition by ItemNo
order by Date DESC) as Row from ItemsInfo)

select T.ItemNo, P.Price as PrevPrice, P.[Date] as PrevDate,
T.Price as CurrentPrice, T.[Date] as CurrentDate
from Cte T LEFT JOIN cte P on T.ItemNo = P.ItemNo and T.Row = 1
and P.Row = 2

is the ';with cte as...' part of the solution? Is this a syntax that only applies to SQL2005 and up? If so, can somebody please explain briefly what that is or just point me in the direction of an article or blog post or even what I would call that to search for further explanation?

Thanks,
Willie

 

[r937]

search "common table expressions"

besides sql server, they are supported by db2, oracle, and postgresql

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[wbodger]

Thanks!

 

[markros]

In addition, you can find this blog post of some help
CTE and Hierarchical queries

PluralSight Learning Library

 

[djj55]

wbodger,
markros is very good with cte. I actually used the above code to develop a solution yesterday.

I have been using cte for a while but still am trying to grasp it as I do not use it every day. ("You can't teach an old dog a new trick" comes to mind.)

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[wbodger]

Yeah, I really enjoy reading thru the posts here as there is always something to learn (not that I can always pick it up right away. Going thru the blog to get a better grasp and see how I can play around with it in my own environment as I take us from SQL Server 2000 to 2008R2...

 

[djj55]

We did the switch a few months ago and it went well. I love 2008R2. We also took the opportunity to upgrade some hardware. Add as much memory as you can and remember you may need to adjust the maximum server memory as SQL takes all it can.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[markros]

BTW, an interesting thing I noticed (although I'm not sure now how valid my tests are).

3 days ago I was testing a complex query on a huge database. It never finished.

I then consulted with Peter Larsson who suggested an interesting approach - and the query executed in seconds.

I tried then to apply his idea to a more complex query - again it didn't work.

Then I tried a hint on the JOIN - again the query that never finished executed in seconds.

I was quite happy with the results, posted that new version, etc.

Then next day by mere accident (I was creating a PIVOT query for a simple problem and it didn't work although the syntax was correct) I found that the compatibility mode of the database was 80.

Ok, I switched it to 90 and started to re-test my queries. Now that MERGE hint I introduced was unnecessary and even made the queries worse (although both still worked rather quickly).

And today I think I found a set of parameters that don't work again.

--------------
So, the lesson learned - don't keep databases in the old compatibility mode because you may get unpredictable results.

PluralSight Learning Library

 

[wbodger]

Hmm...So, what purpose would there be for keeping a database in old compatibility mode? My main SQL Server database is our CRM (Onyx 5.?? I think) that was originally built in SQL Server 7. It was later (before I was around) upgraded to 2000. Now, I am working on upgrading to 2008R2. I know that the Onyx admin console won't work because it uses an MMC snap-in, but might the compatibility mode affect the client connecting?