Query to generate Serial Number (Row Number) in SQL Server

makumbi
 
on Aug 28, 2022 10:47 PM
787 Views

How can i show rank numbers by date using this record sql server

I would like to number these records by on the dates and time stamp

how can i achive this

admno name sex Category dates
22-00003 AHAMAGARA EMMANUEL MARY FEMALE Medication 27/08/2022 21:32:33
22-00004 AKANDINDA FRANKLIN MALE NULL 27/08/2022 20:55:39
22-00007 BAWOOZA JARIANAH MALAIKA FEMALE Doctor's List 28/08/2022 10:08:28
22-00342 NAMUKWAYA TALHA FEMALE NULL 27/08/2022 20:40:09
22-00343 NAMUKWAYA TALHA FEMALE NULL 23/08/2022 00:00:00
22-00344 NAMUKWAYA TALHA FEMALE Doctor's List 27/08/2022 21:37:12
Download FREE API for Word, Excel and PDF in ASP.Net: Download
KasimA
 
on Aug 29, 2022 12:07 AM
on Aug 29, 2022 02:23 AM

Hi makumbi,

Use ROW_NUMBER function in the SELECT statement.

Please refer below sample.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<form id="form1" runat="server">
<asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField HeaderText="Sr No" DataField="SrNo" />
        <asp:BoundField HeaderText="Name" DataField="FirstName" />
        <asp:BoundField HeaderText="Date" DataField="BirthDate" />
        <asp:BoundField HeaderText="City" DataField="City" />
        <asp:BoundField HeaderText="Country" DataField="Country" />
    </Columns>
</asp:GridView>
</form>

Namespaces

C#

using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if(!this.IsPostBack)
    {
        this.BindGridView();
    }
}

private void BindGridView()
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using(SqlConnection con = new SqlConnection(conString))
    {
        using(SqlCommand cmd = new SqlCommand("SELECT ROW_NUMBER() OVER(ORDER BY BirthDate) SrNo,* FROM Employees", con))
        {
            con.Open();
            gvEmployees.DataSource = cmd.ExecuteReader();
            gvEmployees.DataBind();
            con.Close();
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGridView()
    End If
End Sub

Private Sub BindGridView()
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand("SELECT ROW_NUMBER() OVER(ORDER BY BirthDate) SrNo,* FROM Employees", con)
            con.Open()
            gvEmployees.DataSource = cmd.ExecuteReader()
            gvEmployees.DataBind()
            con.Close()
        End Using
    End Using
End Sub

Screenshot