SQL Server ROW_NUMBER() OVER( PARTITION BY

[Rx_]

Got this to work. Would like to learn more about the Over(partition by, does anyone have good suggestions for sites to explain to a beginner?

See my post on how to include data for posting on SQL Server Central:
http://www.access-programmers.co.uk/forums/showthread.php?t=279280
Posting the data made such a difference.
http://www.sqlservercentral.com/Forums/Topic1703912-1292-1.aspx
(see Data and solution)

On the SQL Server Side, the ROW_NUMBER() OVER( PARTITION BY is very powerful for a 2nd column sort. When moving code from MS Access it can replace the IIF or other options.

Here is another article that provides a step-by-step to it.
http://www.midnightdba.com/Jen/2010/10/tip-over-and-partition-by/#comments

Oracle had the Over statement earlier, MS SQL Server adopted it in 2005

There was a good solution for an Oracle person found here:
http://www.access-programmers.co.uk/forums/showthread.php?t=206471
However, my goal is to write views on the SQL Server side - then call the view with the Linked Table. Using a query to filter primary keys works very, very fast across the ODBC layer of the linked table.
The example above is great, however it uses the IIF statement. Custom functions don't perform well over ODBC (linked tables).
The kind of Data Mining in my solution was taking 10 minutes due to the business rules on the client side. It now runs in parts of seconds with all of the logic on the SQL Server side. NOTE: These are reports, read-only, just like a complex summary query.

CREATE VIEW [dbo].[vSHLBHL_SHLPriority]
AS
With vSHLBHL_SHLrows AS(
    SELECT ID_Wells, 
        ID_SHLBHL, 
        Req_Fin_Priority AS MinPriority, 
        Req_Fin, 
        SHLBHL,
        ROW_NUMBER() OVER( PARTITION BY ID_Wells ORDER BY Req_Fin_Priority, ID_SHLBHL DESC) rn
    FROM dbo.vSHLBHL_SHL
)
SELECT ID_Wells, 
    ID_SHLBHL, 
    MinPriority, 
    Req_Fin, 
    SHLBHL
FROM vSHLBHL_SHLrows
WHERE rn = 1

 

[WayneRyan]

Ex,

Free ebook

SQL Server 2012 T-SQL Recipes.

chapter 7 is good.

Wayne