Introduced in 2005.
~Brad
Cool thanks Brad.
Along with CTE, you should also take a look at Partition By that was
added in 2005. It makes things like ranking a hell of a lot simpler:
Judah
Wow I could have used that last year when I did a complex query for something similar, looks like I am going to have to brush up on my SQL skills some what.
2005 and 2008 introduced some really cool stuff. I just got introduced
to Table Valued Parameters. Using that, you can create a User Defined
Table of your values and pass the whole table to a stored procedure:
http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters
We recently rewrote a big import process (hundreds of thousands of
records or more) to use TVP and dropped a 12 hour process down to
something like 12 minutes. Unfortunately, Microsoft has not chosen to
support TVP through their JDBC driver yet, nor has jTDS, so you can't
use TVP from CF. One of those annoying times where I had to admit that
rewriting a task from CF to .Net made a lot of sense. Can't win them
all, I guess.
Judah
You’re killing me Judah, this stuff would have been handy to know for some stuff I was working on in my last job. Shame the JDBC drivers are a bit behind though…
Judah,
I just had a need to use this and found it great, except I need to do something like this
count(Special.specialId) over(partition by specialId where ) as SpecialCount
But I only wont to count those where there expirydates are greater or equal to current date, does the partition support a where clause, or would I best be doing that in the left outer join?
According to the MSDN docs, it looks like the OVER function will only
act on the results of the FROM clause, so you'd want to put your
criteria there, which would mean either a left join or you could do a
Common Table Expression and pre-filter your data there and then refer
to the CTE in your FROM clause.
http://msdn.microsoft.com/en-us/library/ms189461.aspx :
PARTITION BY
Divides the result set into partitions. The window function is applied
to each partition separately and computation restarts for each
partition.
value_expression
Specifies the column by which the rowset produced by the corresponding
FROM clause is partitioned. value_expression can only refer to columns
made available by the FROM clause. value_expression cannot refer to
expressions or aliases in the select list. value_expression can be a
column expression, scalar subquery, scalar function, or user-defined
variable.
Cheers,
Judah
Thanks Judah, I did figure it out that it had to be on the left outer join.
I love this partition stuff.