开发者

Order by clause is changing my result set

I know why it's happening but I want to find a way around it if possible.

For example I have 4 rows in my database and each has a datetime (which are all different). What I want to do is get the latest 2 row开发者_如何学Cs but use ascending order, so that the oldest is at the top of the result set.

I currently am using

SELECT TOP 2 *
FROM mytable
WHERE someid = @something
ORDER BY added DESC

This gets me the correct rows but in the wrong order. If I change the DESC to ASC it gets the right order, but the older two of the four rows. This all makes sense to me but is there a way around it?

EDIT: Solved with Elliot's answer below. The syntax would not work without setting an alias for the derived table however. Here is the result

SELECT * FROM 
(SELECT TOP 2 * FROM mytable WHERE someid = @something ORDER BY added DESC) AS tbl
ORDER BY tbl.added ASC


I'd think one brute-force solution would be:

SELECT *
FROM (SELECT TOP 2 * FROM mytable WHERE someid = @something ORDER BY added DESC)
ORDER BY added


This will allow "top 2 per something" with a PARTITION BY added to the OVER clause

SELECT *
FROM
  (
  SELECT *, ROW_NUMBER() OVER (ORDER BY added DESC) as rn
  FROM mytable
  WHERE someid = @something
  ) foo
WHERE rn <= 2
ORDER BY added

Note that the derived table requires an alias

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜