Hi rani,
You can use DENSE_RANK function instead of ROW_NUMBER function.
Check this example. Now please take its reference and correct your code.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
Controller
public class HomeController : Controller
{
public IActionResult Index()
{
string constr = ".\SQL2014;DataBase=Northwind;UID=sa;PWD=pass@123";
using (SqlConnection con = new SqlConnection(constr))
{
string query = "SELECT TOP 7 CustomerID,ContactName,Country FROM (SELECT DENSE_RANK() OVER(PARTITION BY Country ORDER BY CustomerID DESC) AS RowNo,* FROM Customers) t WHERE t.RowNo = 1";
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
ViewBag.Data = dt;
}
}
}
return View();
}
}
View
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<link rel="stylesheet" media="screen" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.0.3/css/bootstrap.min.css" />
</head>
<body class="container">
<form>
@if (ViewBag.Data != null)
{
<table class="table">
<tr>
<th>ID</th>
<th>Name</th>
<th>Country</th>
</tr>
@foreach (System.Data.DataRow dr in ViewBag.Data.Rows)
{
<tr>
<td>@dr["CustomerID"]</td>
<td>@dr["ContactName"]</td>
<td>@dr["Country"]</td>
</tr>
}
</table>
}
</form>
</body>
</html>
Screenshot
