CODEWITHSUNDEEP
sqlsql-serversql-order-by
James Hay
James Hay

Reputation: 7315

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 rows 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

Upvotes: 2

Views: 230

Answers (2)

gbn
gbn

Reputation: 432261

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

Upvotes: 5

Elliot Nelson
Elliot Nelson

Reputation: 11557

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

Upvotes: 6

Related Questions