get row number of record in resultset sql server

问题:

I have a table full of documents with serial numbers - columns:

• docid int
• writing text
• submissionDate datetime

...and I'd like to find out where a particular serial number lies in a query against the table.

For example, if I ordered the resultset by submissionDate asc, I might want to know where document 34 is within that ordering (i.e. is it in position 11?)

How do I do this?

回答1:

It looks like you're trying to get the row number even if that's not one of the rows being returned. You can use a CTE for that:

;WITH CTE AS
(
    SELECT
        docid,
        writing,
        submissionDate,
        ROW_NUMBER() OVER (ORDER BY submissionDate) AS position
    FROM
        My_Table
)
SELECT
    docid,
    writing,
    submissionDate,
    position
FROM
    CTE
WHERE
    docid = 34

This also requires SQL Server 2005 or greater of course.

回答2:

SQL Server 2005+, using analytic functions:

SELECT t.docid,
       t.writing,
       t.submissiondate,
       ROW_NUMBER() OVER(ORDER BY t.submissiondate) AS position
  FROM YOUR_TABLE t

Non-analytic version

This approach risks non-sequential values because if there are two (or more) records with the same submissiondate value -- they all will have the same position value. It's on par with using RANK or DENSE_RANK analytic functions, because ROW_NUMBER will always grant a unique value for every record without regard for duplicate values.

SELECT x.docid,
       x.writing,
       x.submissiondate,
       (SELECT COUNT(*)
          FROM YOUR_TABLE y
         WHERE y.submissiondate <= x.submissiondate) AS position
  FROM YOUR_TABLE x

回答3:

You can use Window Function rank().

Example (from MSDN):

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,RANK() OVER (ORDER BY i.Quantity DESC) AS 'RANK'
  FROM Production.ProductInventory i 
 INNER JOIN Production.Product p  ON i.ProductID = p.ProductID
 ORDER BY p.Name;

It's very powerful.

It SQL standard and it should work in SQL Server 2005 and newer versions.