CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
DanS
DanS

Reputation: 37

SQL Server - Display rank of row depending on column

I have a query that returns:

RowNumber     Name
===================================
1             Invoice-02116 (1).pdf
1             Invoice-02116 (2).pdf
1             Invoice-02116.pdf
2             Invoice-02116.pdf
3             Invoice-02116.pdf
4             Invoice-02116.pdf
5             Invoice-02116.pdf

What I need is something like:

RowNumber     Name
===================================
1             Invoice-02116 (1).pdf
2             Invoice-02116 (2).pdf
3             Invoice-02116.pdf
3             Invoice-02116.pdf
3             Invoice-02116.pdf
3             Invoice-02116.pdf
3             Invoice-02116.pdf

More exactly, to display the rank depending on the name column.

What I am using for RowNumber column is:

SELECT ROW_NUMBER() OVER (PARTITION BY ff.Name ORDER BY ff.Name) AS RowNumber, ff.Name

Upvotes: 1

Views: 64

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

Use DENSE_RANK with an appropriate order:

SELECT
    DENSE_RANK() OVER (ORDER BY LEN(Name) DESC, Name) AS RowNumber, 
    Name
FROM yourTable;

Demo

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use DENSE_RANK instead :

SELECT DENSE_RANK() OVER (ORDER BY ff.Name) AS RowNumber...

Upvotes: 3

Related Questions