score:5
Accepted answer
ienumerable<downloadhistory> top5results = downloadhistory
// group by songid
.groupby(row => row.songid)
// for each group, select the newest row
.select(grp =>
grp.orderbydescending(historyitem => historyitem.dateinserted)
.firstordefault()
)
// get the newest 5 from the results of the newest-1-per-song partition
.orderbydescending(historyitem => historyitem.dateinserted)
.take(5);
score:0
--it took 99% of the two execution
select top (5) [t3].[songid], [t3].[dateinserted]
from (
select [t0].[songid]
from [dbo].[downloadhistory] as [t0]
group by [t0].[songid]
) as [t1]
outer apply (
select top (1) [t2].[songid], [t2].[dateinserted]
from [dbo].[downloadhistory] as [t2]
where [t1].[songid] = [t2].[songid]
order by [t2].[dateinserted] desc
) as [t3]
order by [t3].[dateinserted] desc
--it took 1% of the two execution
select top 5 t.songid,t.dateinserted
from (select songid,
dateinserted,
row_number()
over(
partition by songid
order by dateinserted desc) rn
from downloadhistory) t
where t.rn = 1
order by dateinserted desc
Source: stackoverflow.com
Related Query
- Linq to SQL | Top 5 Distinct Order by Date
- Linq to SQL order by with Distinct
- Linq to SQL get distinct records grouped by date
- Linq query to order a list based on date and get distinct
- Select distinct and date difference in Linq to SQL from two dataTables
- How to get top 1 record of each group order by desc in SQL Server using linq
- Order by, distinct and select top 5 results with linq
- Distinct by order date and products SQL Server 2008
- LINQ orderby on date field in descending order
- How to write linq query to match SQL like select top 100 * from tab?
- Select top 1 result from subquery in linq to sql
- Linq to SQL using group By, and order by count
- How to use distinct with group by in Linq to SQL
- LINQ to SQL - Why can't you use a WHERE after an ORDER BY?
- Linq to SQL Left Join, Order and Group By Count
- LINQ to SQL Select Distinct by Multiple Columns and return entire entity
- Order by (asc|desc) in linq to SQL Server handles DateTime differently
- Converting SQL containing top, count, group and order to LINQ (2 Entities)
- Distinct Date with Linq
- LINQ order a collection of objects by date in descending order
- linq query distinct date
- Linq To Sql: The member Date has no supported translation to SQL
- Order of execution of LINQ methods returning same results but different SQL in this code. What exactly is going on inside?
- c# Linq select distinct date time days
- Linq to entity order by sql
- LINQ - How to use distinct with order by?
- Linq to XML Noob question - distinct and order by on attributes
- Linq query distinct values of date for last 7 days, exception thrown when running query
- Linq Grouping Order By Date then Time
- Distinct and order list LINQ
More Query from same tag
- How to add dynamic sort by direction to NHibernate queryover
- Instead of FirstOrDefault() what do I use to get all values in group by?
- How to get an object from a list based upon IEqualityComparer<T>
- How to check if any word in my List<string> contains in text
- Right outer join in linq-to-sql
- linq looping through tags with the same name
- InvalidOperationException: The LINQ expression for groupby
- EF Core dynamic filter
- Select objects where id = id from another object
- Entity Framework Linq to SQL expression containing LEFT JOIN
- LINQ Summary of Nested Dictionary
- Query for entities which are not joined with some other certain entities
- How to convert store procedure to linq in nopCommerce c#
- Will AsEnumerable increase efficiency in this example?
- LINQ Intersection of two different types
- LINQ: Sort by (Treat letters as numbers)
- Using LINQ to get a diff between a list and insert it back in between?
- How to remove redundant execution paths in a concatenated linq/lambda expression
- how to outer join in F# using FLinq?
- Problem with my inner join
- List<T> LINQ to JSON
- partial view in mvc core
- Static C# variable, throw an error because of a database connection
- LINQ query to get result
- How to add where statements dynamically by linq
- Can't access to middle table in LINQ with entity framework?
- how to insert two values manually into table by using code
- Convert SQL IN clause with two columns into LINQ
- Loop performance with LINQ clause
- Extracting a list from a list LINQ