SELECT TOP 1 [Name], [Symbol], PriceDate
FROM aaa
GROUP BY [Name], [Symbol]
views:
186answers:
5Select 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.
one way, you can also use a variable instead of 1 since sql server 2005
select top (1) *
from SomeTable
order by SomeColumn
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
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]
)