SELECT BOTTOM without changing ORDER BY

Question

I'm new to SQL and wondering is it possible to select the bottom n rows without using SELECT TOP and then ORDER BY.

I can find numerous examples of mimicing the idea with SELECT TOP & then ORDER BY e.g. How to select bottom most rows? but I need to keep my data sorted in the opposite order so using ORDER BY isn't ideal.

My below query returns the data points I need to pass through to my other programmes but now the data comes out sorted in the opposite direction than desired and causes issues.

SELECT TOP 504 
date
,price

FROM
[dbo].[AssetRet]

WHERE
asset = 'SP500'

ORDER BY
date DESC

I could get round this by putting a filter on date on remove TOP but I'm not sure how to do this i.e.:

WHERE
date > Min(Select DISTINCT TOP 504 date FROM [dbo].[AssetRet] ORDER BY date DESC)

I hope it is possible via SQL to SELECT BOTTOM without ORDER BY; else I will have to just flip the data after the SQL query in the other program

score 2 · Answer 1 · answered Jul 16 '13 at 16:41

I don't fully understand what you're after, but you can use ROW_NUMBER() in place of top/bottom, you can use it to get both TOP and BOTTOM if you wanted:

SELECT date, price
FROM (SELECT     date
               , price
               , ROW_NUMBER() OVER (ORDER BY date ASC) 'RowRankASC'
               , ROW_NUMBER() OVER (ORDER BY date DESC) 'RowRankDESC'
        FROM AssetRet
        WHERE asset = 'SP500'
     )sub
WHERE RowRankASC <= 504
     OR RowRankDESC <= 504
ORDER BY date

score 1 · Accepted Answer · edited May 23 '17 at 12:24

1

You could just wrap it in another query and put in the order you require...

SELECT x.* 
FROM   (
       SELECT TOP 504 
              "date",
              price
       FROM   [dbo].[AssetRet]
       WHERE  asset = 'SP500'
       ORDER BY "date" DESC
       ) x 
ORDER BY x."date"

Does this alternative work?...you will need a later version of sql-server for the partition function RANK...

SELECT x."date",
       x.price
FROM   (
       SELECT "date",
              price,
              Rnk = RANK() OVER (ORDER BY "date" DESC)
       FROM  [dbo].[AssetRet]
       WHERE asset = 'SP500'
       ) x 
WHERE  x.Rnk <= 504
ORDER BY x."date"

EDIT
Looks like this other answer pretty much covered your question

edited May 23 '17 at 12:24

Community

1
1

answered Jul 16 '13 at 16:38

whytheq

32,991
61
166
261

No problem - glad I could help - what version of sql-server are you using? Did the second suggestion not work? – whytheq Jul 17 '13 at 08:58
The second suggestion worked perfectly too, thanks.... first suggestion seems a bit easier to implement – TylerDurden Jul 17 '13 at 08:59

SELECT BOTTOM without changing ORDER BY

2 Answers2