Here is the solution for your query
here i am creating a DataTable
DataTable dt = new DataTable("mytable"); DataColumn dc1 = new DataColumn("ID", typeof(int)); dc1.AutoIncrement = true; dc1.AutoIncrementSeed = 1; dc1.AutoIncrementStep = 1; dt.Columns.Add(dc1); dt.Columns.Add(new DataColumn("name", typeof(string))); dt.Columns.Add(new DataColumn("age", typeof(string))); dt.Columns.Add(new DataColumn("date", typeof(DateTime))); for (int i = 0; i < 10; i++) { DataRow dr = dt.NewRow(); dr["name"] = "dasda " + i.ToString(); dr["age"] = (i * i).ToString(); switch (i) { case 0: dr["date"] = DateTime.Now.AddDays(i); break; case 1: dr["date"] = DateTime.Now.AddDays(2); break; case 2: dr["date"] = DateTime.Now.AddDays(i * i); break; case 3: dr["date"] = DateTime.Now.AddDays(i * i); break; case 4: dr["date"] = DateTime.Now.AddDays(2); break; case 5: dr["date"] = DateTime.Now.AddDays(i * i); break; case 6: dr["date"] = DateTime.Now.AddDays(2); break; case 7: dr["date"] = DateTime.Now.AddDays(i * i); break; case 8: dr["date"] = DateTime.Now.AddDays(i * i); break; case 9: dr["date"] = DateTime.Now.AddDays(2); break; default: break; } dt.Rows.Add(dr); } now i am firing the LINQ query to fetch the records as you want..
var xyz = (from m in dt.Select() let _name = m.Field<string>("name") let _date = m.Field<DateTime>("date") let _age = m.Field<string>("age") let _id = m.Field<int>("id") orderby _date descending group m by _date into g select new { _name = g.First()["name"].ToString(), _id = Convert.ToInt32(g.First()["ID"]), _age = g.First()["age"].ToString(), _date = (DateTime)g.First()["date"] }).ToList(); this query will give the distinct record on the behalf of date.for fetching last ten record i used order by descending. you want top 10 record then simply call TAKE() you wiil get the result.
var res= xyz.Take(10);
you will get the top 10 records.
In this article, we will learn how to use SQL SELECT TOP queries and we will also reinforce this learning using examples.
Introduction
The TOP clause allows us to limit the result set of the queries according to the number of rows or the percentage of rows. In general, the TOP and ORDER BY construction are used together. Otherwise, the TOP clause will return the N number of rows in an uncertain order. For this reason, it is the best practice to use the TOP clause with an ORDER BY to obtain a certain sorted result.
The syntax of the TOP clause is as follows:
SELECT TOP (expression) [PERCENT]
[WITH TIES]
FROM
table_name
Arguments
Expression
This numerical expression defines how many rows are returned from the query. For instance, when we want to return the first 10 rows of the table we can set this parameter as 10. In this example, we retrieve random 5 rows from the Product table.
SELECT TOP (5) Name,
ProductNumber,
StandardCost
FROM Production.Product;
PERCENT
The PERCENT keyword specifies that the query will return rows by %n proportion of the result set. This value must be between 0 and 100. Such as, if we want to retrieve half of the rows in a table, it would be sufficient to set this value to 50. The following query will return 20 percent of rows in the table.
SELECT TOP (20) PERCENT Name,
ProductNumber,
StandardCost
FROM Production.Product;
WITH TIES
The WITH TIES keyword enables to include the rows into the result set that matches with the last row. We need to take into account one point about the WITH TIES, usage of this expression in the queries may cause more rows to be returned than we specify in the TOP expression. For example, if we want to retrieve the highest cost product we can use the TOP 1 keyword. However, if we add the WITH TIES keyword to the SQL SELECT TOP statement, the query will return all rows which have the same cost. WITH TIES keyword must be used with the ORDER BY. Let’s execute the following query and then interpret the result.
SELECT TOP (1) WITH TIES Name,
ProductNumber,
StandardCost
FROM Production.Product
ORDER BY StandardCost DESC
As we can see, the query has returned more than one product whose costs are the same as the first one.
Using the SQL SELECT TOP statement with a variable
Variables are database objects which are used to store data during the execution of the query. In the following query, we assign a value to the variable and the query will return the first rows that equal the variable assigned value.
equal the variable assigned value.
DECLARE @Val AS INT
SET @Val \=3
SELECT TOP(@Val) Name,
ProductNumber,
StandardCost
FROM Production.Product
ORDER BY StandardCost DESC
- Tip: The TOP clause accepts 0 to bigint (9223372036854775807) as an argument. For example, the following query will return an error because the argument is bigger than the maximum value of the bigint
SELECT TOP(9223372036854775808) Name,
ProductNumber,
StandardCost
FROM Production.Product
ORDER BY StandardCost DESC
As has been pointed out in the above image, the query has returned an arithmetic overflow error.
Using SQL UPDATE TOP statement
When we use a TOP clause with an update statement, the update runs for the undefined rows because we could not add ORDER BY to this type of update statement. The below query demonstrates this kind of usage.
UPDATE TOP (10) Production.ProductListColors
SET Color \= 'Pink'
At the same time, we can use the PERCENT keyword on the update statements. The following query will change half of the rows in the table.
UPDATE TOP (50) PERCENT Production.ProductListColors
SET Color \= 'Pink'
Using SQL DELETE TOP statements
We can use the TOP statement in the delete queries. The following query deletes a random row from the ProductListColors table.
DELETE TOP (1)
FROM Production.ProductListColors;
If we want to delete the first row of the table according to a certain order, we can use CTE’s to perform this type of requirement. Through the following query, we can delete a row that has the biggest ProductID.
WITH DelCTE AS (
SELECT TOP 1 * FROM Production.ProductListColors
ORDER BY ProductID DESC )
DELETE FROM DelCTE
SQL SELECT TOP statement vs SET ROWCOUNT option
SET ROWCOUNT option limits the number of rows that return from a query. When we set SET ROWCOUNT as 4 for the following query, it processes the whole query but only returns 4 rows.
SET ROWCOUNT 4
SELECT Name,
ProductNumber,
StandardCost
FROM Production.Product
SET ROWCOUNT option overrides the TOP keyword, if the set value of the ROWCOUNT is smaller than the TOP expression, the returned number of rows will be equal to the ROWCOUNT option. For example, the following query will only return 2 rows.
SET ROWCOUNT 2
SELECT TOP(5) Name,
ProductNumber,
StandardCost
FROM Production.Product
The main difference between the SQL SELECT TOP statement and the SET ROWCOUNT option is that the SET ROWCOUNT option does not consider by the query optimizer and its performance might be worse than the TOP clause. Despite that, TOP clauses get involved in the query plans of the queries.
SQL SELECT TOP statement vs ROW_NUMBER function
ROW_NUMBER function helps to give temporary numbers to the result set of the query and it can be also used instead of the TOP clauses. For example, the following query will return the first 4 rows of the Product table.
SELECT * FROM (
SELECT Name,
ProductNumber,
StandardCost,
ROW_NUMBER() OVER (ORDER BY StandardCost DESC) AS RN
FROM Production.Product
) AS TMP_TBL
WHERE RN<=4
Conclusion
In this article, we have talked about the SQL SELECT TOP statements, this query structure helps to limit the result set of the queries. At the same time, we have learned some alternatives to the TOP clauses.
Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
How do I select top 5 rows in LINQ?
LINQ Query To Select Top 5 Records..
var list = (from t in ctn.Items..
where t.DeliverySelection == true && t.Delivery.SentForDelivery == null..
orderby t.Delivery.SubmissionDate..
select t). Take(5);.
How to get the first item in LINQ?
C# Linq First() MethodUse the First() method to get the first element from an array. Firstly, set an array. int[] arr = {20, 40, 60, 80 , 100}; Now, use the Queryable First() method to return the first element.
What is the difference between single first and take 1 in LINQ?
firstOrdefault() in LINQ Query. Single() - There is exactly 1 result, an exception is thrown if no result is returned or more than one result. SingleOrDefault() – Same as Single(), but it can handle the null value. First() - There is at least one result, an exception is thrown if no result is returned.
What is first () in C#?
The LINQ First Method in C# returns the first element from a data source or a collection.