How do you Select the Top record from a Group of Rows from a Table in a database?

Answer 1

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

Answer 2

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

SELECT TOP N *
FROM myTable
ORDER BY anyFields

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

Answer 3

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

select top (1) * 
from SomeTable
order by SomeColumn

Answer 4

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

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.

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

Answer 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]
)