How to rank SQL Server table entries by field criteria using primary key-CodePudding

I have an Access database that uses a SQL Server back end, and I have written a query that ranks entries in the Panels table by AgreementID column, using the PanelID primary key to determine ranking.

The query in MS Access is this:

SELECT 
    X.AgreementID, X.PanelID, X.Panel_TypeID, 
    ((SELECT COUNT(*) 
      FROM Panels 
      WHERE (PanelID < X.PanelID) 
        AND (AgreementID = X.AgreementID);)   1) AS Rank
FROM 
    Panels AS X
WHERE 
    (((X.AgreementID) IS NOT NULL))
ORDER BY 
    X.AgreementID, X.PanelID;

The output from this looks like this:

As you can see, each entry related to the AgreementID is ranked according to its PanelID.

The problem I have is there are 20k rows and it takes too long to run in Access, so I was hoping to recreate this in the back end in the hopes that it would run faster than using linked tables in the front end.

The method above doesn't seem to be supported in SQL Server, so I need some pointers to help me solve this.

CodePudding user response：

Running a simple subquery like that should work fine in SQL Server. Assuming your back-end table has the same schema as your Access table, try removing a few of the parentheses and running the same query:

SELECT 
    X.AgreementID, 
    X.PanelID, 
    X.Panel_TypeID, 
    (
    SELECT 
        COUNT(*) 
    FROM 
        Panels 
    WHERE 
        PanelID < X.PanelID
        AND AgreementID = X.AgreementID
    )   1 AS Rank
FROM 
    Panels X
WHERE 
    X.AgreementID IS NOT NULL
ORDER BY 
    X.AgreementID, 
    X.PanelID
;

CodePudding user response：

You could use a subquery in SQL Server. But there are better ways to do this.

You can use a row-numbering function.

SELECT 
    p.AgreementID,
    p.PanelID,
    p.Panel_TypeID,
    ROW_NUMBER() OVER (PARTITION BY p.AgreementID ORDER BY p.PanelID) AS Rank
FROM 
    Panels p
WHERE 
    p.AgreementID IS NOT NULL
ORDER BY 
    p.AgreementID, 
    p.PanelID
;

The logic of this is slightly different as it will only count non-null rows. If you want to count all rows first then you need this:

SELECT 
    p.AgreementID,
    p.PanelID,
    p.Panel_TypeID,
    p.Rank
FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY p.AgreementID ORDER BY p.PanelID) AS Rank
    FROM
        Panels p
) p
WHERE 
    p.AgreementID IS NOT NULL
ORDER BY 
    p.AgreementID, 
    p.PanelID
;

For these queries to perform efficiently, you will need an index.
Either a clustered index

Panels (AgreementID, PanelID)

Or a non-clustered index with include columns

Panels (AgreementID, PanelID) INCLUDE (Panel_TypeID)