如何从数据库中的表中选择一组行中的最高记录？

I am SELECTing a group of records but one column yields distinct values in two or more results. I just want to SELECT the top value; not the Min one or the Max value, but the Top N value encountered by SQL Server 2008. Is there any aggregation mechanism that will perform this?

我正在选择一组记录,但是一列在两个或多个结果中产生不同的值。我只想选择最高值;不是最小值或最大值,而是SQL Server 2008遇到的前N值。是否有任何聚合机制将执行此操作?

E.g.

Table has:

Microsoft MSFT 12/21/05 Microsoft MSFT 10/22/05 Microsoft MSFT 11/23/06 Paramount PMNT 02/21/05 Paramount PMNT 01/23/06 I execute:

SELECT [Name], [Symbol], PriceDate
FROM aaa
GROUP BY [Name], [Symbol]

The desired result is:

Microsoft MSFT 12/21/05 Paramount PMNT 02/21/05

表包含:Microsoft MSFT 12/21/05 Microsoft MSFT 10/22/05 Microsoft MSFT 11/23/06 Paramount PMNT 02/21/05 Paramount PMNT 01/23/06执行:SELECT [Name],[Symbol], PriceAate FROM aaa GROUP BY [名称],[符号]所需的结果是:Microsoft MSFT 12/21/05 Paramount PMNT 02/21/05

(TOP fails)

5 个解决方案

#1

Hm, I think all the answers do NOT answer the question (but of course maybe I got the question wrong):

嗯,我认为所有的答案都没有回答这个问题(但当然也许我的问题是错的):

If you do not group, you might e.g. get MSFT twice so we start with something like this

如果你没有分组,你可能会例如得到MSFT两次,所以我们从这样开始

select name, symbol, x(date)
from sometable 
group by name, symbol

The question as I get it is concerned with the function x() which is to return the first element of the date column in the respective group. The problem is: there is no natural order of rows in a relational database. So such a function can't exist, since it is not defined.

我得到它的问题涉及函数x(),它返回相应组中日期列的第一个元素。问题是:关系数据库中没有自然的行顺序。所以这样的功能不能存在,因为它没有定义。

You need another column defining the order, e.g. the column timestamp:

您需要另一列定义订单,例如列时间戳:

select 
    a.name, 
    a.symbol, 
    (
        select b.date 
        from sometable 
        where b.timestamp = min(a.timestamp) 
        and a.name = b.name
        and a.symbol = b.symbol
    ) as first_date
from sometable as a
group by name, symbol

This at least works in oracle. If sqlserver doesn't like this one can rewrite it as a join. The alternative would be analytic functions which I was told are supported by sqlserver

这至少在oracle中有效。如果sqlserver不喜欢这个,可以将它重写为连接。替代方案是分析函数,我被告知sqlserver支持

#2

Select TOP doesn't make sense if you don't specify the order (with ORDER BY smt), so you wanna do smt like:

如果你没有指定顺序(使用ORDER BY smt),选择TOP是没有意义的,所以你想做smt像:

SELECT TOP N *
FROM myTable
ORDER BY anyFields

You'll possibly get inconsistent results (there's nothing assuring the opposite) without order by.

没有顺序,你可能会得到不一致的结果(没有什么可以保证相反)。

#3

one way, you can also use a variable instead of 1 since sql server 2005

一种方式,你也可以使用一个变量而不是自sql server 2005以来的1

select top (1) * 
from SomeTable
order by SomeColumn

#4

SELECT TOP 1   [Name], [Symbol], PriceDate
FROM     aaa
GROUP BY [Name], [Symbol]

#5

SELECT [Name], [Symbol], PriceDate FROM aaa WHERE PriceDate = ( SELECT Top 1 aaa_2.PriceDate FROM aaa aaa_2 WHERE aaa_2.[Name] = aaa.[Name] AND aaa_2.[Symbol] = aaa.[Symbol] )

SELECT [Name],[Symbol],PriceDate FROM aaa WHERE PriceDate =(SELECT Top 1 aaa_2.PriceDate FROM aaa aaa_2 WHERE aaa_2。[Name] = aaa。[Name] AND aaa_2。[Symbol] = aaa。[Symbol])

#1