select S.* from Singles S join ( select max(SingleId) as SingleId from Single group by ArtistId) S2 on S2.SingleId = S.SingleId order by Released desc
该表如下所示:
----------- | Singles | |-----------| | SingleID | | ArtistId | | Released | | Title | | ..... | -----------
等等…
并包含例如这些项目:
SingleID ArtistID Released Title 1 1 2011-05-10 Title1 2 1 2011-05-10 Title2 3 2 2011-05-10 Title3 4 3 2011-05-10 Title4 5 4 2011-05-10 Title5 6 2 2011-05-10 Title6 7 3 2011-05-10 Title7 8 5 2011-05-10 Title8 9 6 2011-05-10 Title9
所以我想要获得最新的单曲,但每个艺术家只有一个.谁能帮助我? 🙂
也许甚至有更好的方法来编写查询?
更新:
要回答评论中发布的问题:
我们使用的是Microsoft SQL Server和LINQ to NHibernate.
这是我们现在正在使用的示例,它返回最新的单曲,而不是由artistid分组:
public Single[] GetLatest() { IQueryable<Single> q; q = from s in _sess.Query<Single>() where s.State == State.Released orderby s.Released descending select s; return q.Take(20).ToArray(); }
var firstSingles = Singles.GroupBy(x => x.ArtistId) .Select(g => g.OrderByDescending(x => x.Released).First()) .ToList();