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
精彩评论