Row_Number() Over Partition

N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
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.

Comments

  • zidianzidian Member Posts: 132
    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
    
    WGU BS-IT Software | Completed 9/30/2014
  • NotHackingYouNotHackingYou Member Posts: 1,460 ■■■■■■■■□□
    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
    When you go the extra mile, there's no traffic.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    Just curious

    How do you order by? I am unable to order by in the CTE and the Query itself.
  • NotHackingYouNotHackingYou Member Posts: 1,460 ■■■■■■■■□□
    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
    When you go the extra mile, there's no traffic.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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
  • NotHackingYouNotHackingYou Member Posts: 1,460 ■■■■■■■■□□
    Order by isn't allowed within the CTE declaration but it is allowed with the temp table
    When you go the extra mile, there's no traffic.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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?
  • NotHackingYouNotHackingYou Member Posts: 1,460 ■■■■■■■■□□
    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
    When you go the extra mile, there's no traffic.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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.
  • UniqueAgEnTUniqueAgEnT Member Posts: 102
    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.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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.
Sign In or Register to comment.