Search code examples
sqlsql-serverlistcountrow-number

How to use count() or Row_number() for SQL Server

I'm currently trying to figure out a method to show the row number or count of items in a table. I need a SQL statement for Row_number or count or something like this to get the ID column. Here's what I'm looking for:

| Col1 | Col2 | Col3 | ID |
 -------------------------
    A     1/9   2/1     1
    A     1/9   2/2     1
    A     1/9   2/3     1
    B     2/1   2/1     2
    B     2/1   2/2     2
    B     2/1   2/3     2
    C     2/6   2/1     3
    C     2/6   2/2     3
    C     2/6   2/3     3
    C     2/6   2/3     3

So Basically I want to count by col1 and order by col3(date column) to get the ID

Thanks

Solution

  • You can use dense_rank(), I think:

    select t.*, dense_rank() over (order by col1) as id
from t;