sql数据重复查询问题

[a102470]

我想查询出一个表中不重复的数据，重复的取articleid 最大的，按照articleid 的倒叙排列，我是下面的写法，结果运行出来不对。
SELECT Publicaccounts,articleid FROM article GROUP BY Publicaccounts,articleid order by articleid desc

[昵称90天可改一次]

SELECT Publicaccounts,
		articleid=MAX(articleid)  
FROM article 
GROUP BY Publicaccounts 
order by articleid  desc

[--小F--]

select * from article  as t where not exists(select 1 from article where Publicaccounts=t.Publicaccounts and articleid>t.articleid)  order by articleid desc

[a102470]

引用 2 楼 fredrickhu 的回复:

select * from article  as t where not exists(select 1 from article where Publicaccounts=t.Publicaccounts and articleid>t.articleid)  order by articleid desc

那个方法挺好用，但是我后来修改成这样了，报“除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。”这个错误，请问该如何修改呀？ select * from ( select *, ROW_NUMBER() over( order by AutoId ) row from article as s where 1=1 and not exists(select 1 from article where Publicaccounts=s.Publicaccounts and articleid>s.articleid) order by articleid desc) t where row between 1 and 20

[--小F--]

试试：
select
 * 
from
  ( select *, ROW_NUMBER() over( order by AutoId,order by articleid desc ) row from article as s where 1=1  and not exists(select 1 from article where Publicaccounts=s.Publicaccounts and articleid>s.articleid)  ) t
where
  row between 1 and 20

[a102470]

引用 4 楼 fredrickhu 的回复:

试试：
select
 * 
from
  ( select *, ROW_NUMBER() over( order by AutoId,order by articleid desc ) row from article as s where 1=1  and not exists(select 1 from article where Publicaccounts=s.Publicaccounts and articleid>s.articleid)  ) t
where
  row between 1 and 20

加上count后该如何改呢？ select count(1) from article as t where 1=1 and not exists(select 1 from article where Publicaccounts=t.Publicaccounts and articleid>t.articleid) order by articleid desc ;