选择BOTTOM而不更改ORDER BY

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

我是SQL新手,想知道是否可以在不使用SELECT TOP然后使用ORDER BY的情况下选择底部的n行

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.

我可以找到许多用SELECT TOP然后ORDER BY模仿这个想法的例子,例如如何选择最底行?但我需要按相反的顺序对数据进行排序,因此使用ORDER BY并不理想。

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

我可以通过在删除TOP上设置过滤器来解决这个问题,但我不知道该如何做到这一点,即

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

I'm hoping it is possible via SQL to SELECT BOTTOM without ORDER BY or else I will have to just flip the data after the SQL query in the other programme

我希望通过SQL可以在没有ORDER BY的情况下选择BOTTOM,否则我将不得不在其他程序中的SQL查询之后翻转数据

2 个解决方案

#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...

这个替代方案有用吗?...你需要一个更高版本的sql-server用于分区功能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

编辑看起来这个其他答案几乎覆盖了你的问题

#2

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:

我不完全理解你所追求的是什么,但你可以使用ROW_NUMBER()代替top / bottom,你可以使用它来获得TOP和BOTTOM:

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

#1