Row_Number() Over Partition

N2IT

So this concept is pretty easy once you use this strategy to build a query. I was wondering if you can use the output as (column name) in the where clause to filter or does this have to be capture in a subquery to filter out the 1 , 2 etc.

I want to do a max date on some data. Instead of writing a subquery getting max date, I was going to use this technique to assigne a row number to the date ranges and using the row number filtering my rows making the report use able.

Right now I do getdate max < max date. This work right now, but for non programmers it's hard to understand. I suppose row number over partition isn't much easier LOL.

zidian

Analytic functions cannot be evaluated in the WHERE clause of the current window. If you want to sort, you will need to do a query like this

SELECT *
FROM (
  SELECT ROWNUMBER() OVER (PARTITION BY emp.jobtitle)
  FROM Employee emp
) DerivedTable
WHERE DerivedTable.RowNumber < 2

NotHackingYou

CTE to the rescue!

WITH results AS(
Select column1, column2, ROW_NUMBER() OVER (PARTITION BY column3 ORDER BY column3) AS 'POSITION'
FROM myTable
)

SELECT * FROM results WHERE position > 2

N2IT

Carl I love CTE's but they seem to be way slower than temp tables. Have you noticed this in your environment?


BTW Thanks for the suggestions.

N2IT

Just curious

How do you order by? I am unable to order by in the CTE and the Query itself.

NotHackingYou

I haven't noticed any performance difference in a CTE vs an ad-hoc temp table. I do use temp tables with indexes for very large temp data sets, haven't tried a very large data set with a CTE.

You cannot order by in a CTE - you have to order by when you select from it. If you need to order by as you create the result set you can use a temp table like this




Select column1, column2, ROW_NUMBER() OVER (PARTITION BY column3 ORDER BY column3) AS 'POSITION'
INTO #resultsTable
FROM myTable
ORDER BY column1


SELECT * FROM #resultsTable WHERE position > 2

N2IT

Strange

When I went to temp table like you suggested I was able to order by. I compared the results with the CTE and it was the same.

Is there a reason why the order by wouldn't work using with but putting it into a # table allowed me to order by.

Thoughts?

Carl - See I am not ready for the exam yet 461 the eternal certification lol

NotHackingYou

Order by isn't allowed within the CTE declaration but it is allowed with the temp table

N2IT

CarlSaiyed wrote: »

Order by isn't allowed within the CTE declaration but it is allowed with the temp table

Just to make sure I am clear.

You can't order by the internal CTE query or the select against the CTE?

NotHackingYou

You cannot place an order by inside the CTE-populating query. You can order by with your select against the CTE. Consider these examples:

--Valid syntax for a CTE
WITH results AS(
SELECT * FROM myTable WHERE column = value
)

SELECT * FROM results ORDER BY column2


--Invalid syntax for CTE
WITH results AS(
SELECT * FROM myTable WHERE column = value ORDER BY column2 --cannot order by here
)

SELECT * FROM results

N2IT

Your top example is what I tried. I put the order by in the second select.

I had Select with row_order like we discussed, some case statements with aggregates, max(date range) and several joins. I also had a group by for the non aggregate selects. However in the query I was unable to order by after the group by. It was weird.

UniqueAgEnT

Cte will be slower for larger datasets since the execution plans estimate rows as 0, whereas temp tables will be closer to actual. This causes poor plan selection when compiling and executing.

N2, feel free to post your cte query. You can order by outside of a cte, but not within the cte.

I usually use temp tables unless I am working with recursion or hierarchies.

N2IT

Unique thanks for the explanation.

When I am building proof of concepts I use Excel query usually first, which forces me to use CTE's instead of temp tables. You are so right though, the data sets I deal with can be massive (transactional data) so the temp table is a far better option.

Either way I'll keep working on it as time permits. I have another report to build that went to the top of my list. Yay.